utPLSQL 3.0 – How To Have Your Cake and Eat It

This article was originally published on mikesmithers.wordpress.com.

“You can’t have your cake and eat it!” This seems to be a regular refrain from the EU in the ongoing Brexit negotiations. They also seem to be a bit intolerant of “cherry picking”. I’ve never really understood the saying, “You can’t have your cake and eat it” – What’s the point in having the cake unless you are going to eat it ? Fortunately, I’m not alone in my perplexity – just ask any Brexiteer member of the British Cabinet. For those who want to make sense of it ( the saying, not Brexit), there is a handy Wikipedia page that explains all.

When it comes to Unit Testing frameworks for PL/SQL, compromise between cake ownership and consumption is usually required. Both utPLSQL 2.0 and ruby-plsql-spec have their good points, as well as some shortcomings. Of course, if you want a more declarative approach to writing Unit Tests, you can always use TOAD or SQLDeveloper’s built-in tools.

Recently, a new player has arrived on the PL/SQL testing scene. Despite it’s name, utPLSQL 3.0 appears to be less an evolution of utPLSQL 2.0 as a new framework all of it’s own. What I’m going to do here, is put utPLSQL 3.0 through it’s paces and see how it measures up to the other solutions I’ve looked at previously. Be warned, there may be crumbs…

Installation and Setup

If you’re comfortable on the command line, you can follow the instructions in the utPLSQL 3.0 documentation. On the other hand, if you’re feeling old-fashioned, you can just head over to the Project’s GitHub page and download the latest version. At the time of writing this is 3.0.4.

The downloaded file is utPLSQL.zip.

Now to unzip it. In my case, on Ubuntu, things look like this…

The archive will have unzipped into a directory called utPLSQL.

We now have some decisions to make in terms of how we want to install the framework. To save a bit of time, I’m going to go with the default.

Essentially this is :

DBMS_PROFILER is used by the framework to provide testing coverage statistics, more of which later.

Note that the documentation includes setup steps that provide you with a bit more control. However, if you’re happy to go with the default then you simply need to run the appropriate script as a user connected as SYSDBA…

We should now have a schema called UT3 which owns lots of database objects…

One subtle difference that you may notice between utPLSQL 3.0 and its predecessor is the fact that the default application owner schema has a fairly “modest” set of privileges:

However, the default password for this account is known…

Whilst it’s true that, as a testing framework, utPLSQL should be deployed only in non-production environments you may nevertheless find it prudent to lock the account immediately after installation…

…and possibly even change the password for good measure.

Annotations and Matchers

There are two main component types in a utPLSQL 3.0 unit test – Annotations and Matchers. Annotations allow the framework to identify packaged procedures as tests and (if required), group them into suites. This obviates the need for separate storage of configuration information. Matchers are used to validate the results from a test execution.

This explanation would probably benefit from an example…

The package begins with the suite annotation to identify it as a package that contains unit tests.

The text in brackets displays when the test suite is executed. The positioning of this annotation is important. It needs to be the first thing in the package after the CREATE OR REPLACE statement.
Also, as it’s a package level annotation, it needs to have one or more blank lines between it and any procedure level annotations.

Each of the procedures in the package is identified as an individual test:

Once again the text will display when the test is executed. In the package body, we can see the matchers come into play:

First impressions are that the code seems to have more in common with ruby-plsql-spec than it does with utPLSQL 2.0. This impression is reinforced when we execute the tests…

I was going to re-introduce the Footie app at this point as I’ve used it to demonstrate all of the other PL/SQL testing frameworks I’ve looked at so far. However, in these unprecedented times, I feel that an unprecedented (and very British) example is called for. Therefore, I humbly present…

The Great Brexit Bake-Off Application

The application owner is one hollywoodp (the observant among you will have already noticed that Mary Berry is the DBA)…

The application consists of some tables:

…and a package:

Now, while Mr Hollywood is a renowned TV Chef, his PL/SQL coding skills do leave a little to be desired. Also, the application in it’s current state is just about the minimum he could come up with to demonstrate the framework, which is, after all, why we’re here. Therefore, I’d ask you to overlook the lack of anchored declarations etc. because, before we put the oven on, we need to make a fairly important design decision.

Where should I put my tests?

According to the documentation, the default for utPLSQL is to have the tests located in the same schema as the code they are to run against. However, you may well have good reasons for wanting to keep the tests in a separate schema.

For one thing, you may want to ensure that the process to promote your codebase through to Test and Production environments remains consistent and that you don’t have to worry about taking specific steps to ensure that your test code ends up somewhere it shouldn’t. Additionally, you may find it useful to create “helper” packages for your unit tests. These packages won’t themselves contain tests but will need to be treated as part of your test codebase rather than the application codebase.

If you decide to go down this route with utPLSQL, then you will have to ensure that the schema that owns your tests has the CREATE ANY PROCEDURE privilege if you want to avail yourself of the code coverage reporting provided by the framework. This privilege does not need to be granted if the application owning schema also holds the tests.

I really would prefer to have my tests in an entirely separate schema. So, I’ve created this schema as follows:

The test schema also requires privileges on all of the Application’s database objects :

Run this and we get …

Finally, we’re ready to start testing our application…

Testing Single Row Operations

First, we’re going to write some tests for the BAKE_OFF.ADD_CONTESTANT procedure. So, in the utp_bakeoff schema, we create a package

Before we take a look at the package body, it’s worth pausing to take note of the %suitepath annotation.
This Annotation allows separate test packages to be grouped together. In this instance, I’ve defined the path as Application Name/Package Name. Note that if you want to use this annotation then it must be on the line directly after the %suite annotation in the package header. Otherwise utPLSQL won’t pick it up.

Now for the test package body…

The structure of the tests is quite familiar in that there are four distinct phases, the first three of which are explicit :

  • Setup – prepare the system for the test
  • Execute – run the code to be tested
  • Verify – check the result
  • Teardown – reset the system to the state it was in prior to the test being run

Note that, in this instance, we are using the default behaviour of the framework for the teardown. This involves a savepoint being automatically created prior to each test being run and a rollback to that savepoint once the test completes. Later on, we’ll have a look at circumstances where we need to handle the Teardown phase ourselves.

The first test – add_contestant – uses a helper function and a boolean matcher :

The second test is checking both that we get an error when we try to add a duplicate record and that the error returned is the one we expect, namely :

As we’re expecting the call to the application code to error, we’re using a nested block :

If we now run the test, we can see that our code works as expected.

Incidentally, we can also see how utPLSQL recognises the hierarchy we’ve defined in the suitepath. While this approach works just fine for single-row operations, what happens when the framework is confronted with the need for…

Testing Ref Cursor values

This is always something of an ordeal in PL/SQL test frameworks – at least all of the ones I’ve looked at up until now. Fortunately utPLSQL’s equality matcher makes testing Ref Cursors as simple as you feel it really should be…

Run this and we get :

Incidentally, you may notice that the call to ut.run in this instance is a little different to what I was using previously. There are a number of ways to execute one or more utPLSQL tests through the ut.run procedure and we’ll be taking a look at some of these in a little while.

Testing across Transaction Boundaries

In this case, we’re testing the bulk upload of records from a file into the application tables via an external table. The load itself makes use of the LOG ERRORS clause which initiates an Autonomous Transaction in the background. This means we’re going to need to handle the teardown phase of the tests ourselves as utPLSQL’s default rollback-to-savepoint operation will not do the job.

First of all, here’s a quick reminder of the BAKE_OFF.UPLOAD_CONTESTANTS procedure that we want to test:

As part of the setup and teardown for the test, we’ll need to do a number of file operations – i.e.

  • backup the existing data file
  • create a test file for the external table
  • remove the test file
  • move the original file (if any) back into place

As we may have other loads we want to test this way in the future, then it would seem sensible to separate the code for these file operations into a helper package:

Remember, as we’ve decided to hold all of our test code in a separate schema, we don’t have to worry about distinguishing this package from the application codebase itself.

Now for the test. In the package header, we’re using the rollback annotation to let utPLSQL know that we’ll look after the teardown phase manually for any test in this package :

Now for the test code itself. There’s quite a bit going on here. In the setup phase we:

  • back up the target application table and it’s associated error table
  • generate the file to be uploaded
  • populate ref cursors with the expected results

In the verification phase, we use the to_equal matcher to compare the expected results refcursors with the actual results ( also ref cursors). Finally, we re-set the application to it’s state prior to the test being executed by :

  • removing test records from the application and error tables
  • dropping the backup tables
  • tidying up the data files

All of which looks something like this :

Running tests and reporting results

The framework does offer an API for use to execute tests programatically. However, whilst you’re writing the tests themselves, you’ll probably want something a bit more interactive.

You can simply run all of the tests in the current schema as follows :

However, there are times when you’ll probably need to be a bit more selective. Therefore, it’s good to know that utPLSQL will let you execute tests interactively in a number of different ways:

If we run this for the application tests we’ve written, the output looks like this:

By default ut_run uses the ut_document_reporter to format the output from the tests. However, there are other possible formats, which you can invoke with a second argument to UT_RUN.

For example…


By contrast, if you want something slightly more colourful…

…or even…

Note that, unlike the previous executions, the a_color_console parameter is being passed by reference rather than position. Provided your command line supports ANSICONSOLE, you are rewarded with…

Test Coverage reporting

As mentioned a couple of times already, utPLSQL does also provide coverage reporting functionality. In this case, we’re going to look at the HTML report.

Opening the file in a web browser we can see some summary information :

Clicking on the magnifying glass allows us to drill-down into individual program units:

Of course, you’ll probably want to get an overall picture of coverage in terms of all tests for the application code. In this case you can simply run:

When we look at this file in the browser, we can see that at least we’ve made a start:

Keeping track of your Annotations

Whilst annotations provide a method of identifying and organising tests in a way that avoids the need for storing large amounts of metadata, it can be easy to “lose” tests as a result. For example, if you have a fat-finger moment and mis-type a suitepath value, that test will not execute when you expect it to ( i.e. when you run that suitepath).

Fortunately, utPLSQL does keep track of the annotations under the covers, using the UT_ANNOTATION_CACHE_INFO and UT_ANNOTATION_CACHE tables. Despite their names, these are permanent tables:

So, if I want to make sure that I haven’t leaned on the keyboard at an inopportune moment, I can run a query like:

…which in my case returns…

Final Thoughts

I’ve tried to give some flavour of what the framework is capable of, but I’ve really just scratched the surface.
For more information, I’d suggest you take a look at the framework’s excellent documentation.

Also, Jacek Gebal, one of the authors of the framework has shared a presentation which you may find useful.

The utPLSQL 3.0 framework is a very different beast from it’s predecessor. The ground up re-write of the framework has brought it bang up to date in terms of both functionality and ease of use. If you’re looking for a PL/SQL testing framework that’s contained entirely within the database then look no further… unless you’re allergic to cherries.