PL/SQL is Oracle’s procedural extension to the SQL programming language. It has been said that there is not much of a test culture in PL/SQL. To combat this, renowned book author Steven Feuerstein created the utPLSQL system in 2000. It implements automated unit testing of PL/SQL code. It originally was developed against database version Oracle 8i. Feuerstein released utPLSQL as an open source project.
The current version of utPLSQL is 3.0. Strangely enough, release 3.0 was a complete rewrite of version 2.0. It is not an evolution of the older product. It is effectively a whole new framework. There is a utility to migrate from v2 to v3.
utPLSQL can be installed using a script provided with the project. By default, it produces a log file in the same directly as the installation script. All project objects are created in a schema named UT3. It will create DBMS_PROFILER tables. DBMS_PROFILER is a package provided by Oracle which covers statistics. It also adds public synonyms so that anyone can execute code in the framework.
To install utPLSQL, the user must have a host of database privileges. This user will need standard stuff such as create session and create procedure privileges. The user will also need to have alter session privilege. The user needs execute privilege on the dbms_lock and dbms_cypto Oracle packages.
The testing in utPLSQL is done via a test package that you create. The name of the package must begin with “ut_”. This package must have a ut_setup() and ut_teardown() function defined in it. The tests are procedures placed in this package that have no parameters. The tests are executed by calling test().
Tests are configured using annotations in the PL/SQL code. These annotations exist only in the package specification. There is one package level annotation that must be the first line in the package spec. There must be a blank line that follows this package level annotation. The other annotations identify procedure in the package as being tests. Tests can be grouped into suites. You can set the namespace for the tests using the “suitepath” annotation.
utPLSQL has the ability to compare compound data types in the tests. This includes meaty objects such as ref cursors, object types, nested tables, and VARRAYs.
Tests can be placed in same schema as code being tested. Or they can be put in a separate helper package. The overall structure of the tests is:
1. Setup
2. Execute
3. Verify
4. Teardown
The teardown can be implemented by rolling back any changes from the transaction. Or you can back up the affected tables first. Then restore them once the tests are complete. Tests should not modify the environment in which they are run.
There are two ways to runs the tests. You can call ut.run(), which executes all tests in the schema. If you pass in a specific procedure name as an argument, run() will execute just that one test. You will need to set serveroutput to “on” to see the test results. A second method to run the tests is to call utl_runner.run(). It only runs procedures, not functions. It does not output results to the screen. This is meant to be called as an API. The outcome of tests can be found in UTL_OUTCOME.
Like PLSQL, everything in utPLSQL is case insensitive. Tests are executed in alphabetical order of procedure name. Only public members are executed. You have full access to all of PL/SQL in your test code. utPLSQL is written to catch most exceptions thrown by the code being tested. It will record a clean stack trace, in that any utPLSQL plumbing is hidden.
utPLSQL pay specific attention to code coverage. It can be calculated on the basis of the whole schema, or on a specific set of files in your project. You specify a reporter to format the code coverage report as a parameter to the ut.run() call. There is a built-in reporter that comes with utPLSQL. Or you can specify a third-party version such as Sonar or TeamCity.
1 year ago
