Automated Testing Frameworks and General Rule-Breaking in PL/SQL

If there’s one thing that 2016 has taught us is that rules (and in some cases, rulers) are made for breaking. Oh, and that it’s worth putting a fiver on when you see odds of 5000-1 on Leicester winning the League.

Having lacked the foresight to benefit from that last lesson, I’ve spent several months looking at Unit Testing frameworks for PL/SQL. In the course of this odyssey I’ve covered:

Update : utPLSQL version 3 is now with us. It is very different to version 2. If you want to know more then have a look here.

This post is a summary of what I’ve learned from this exercise, starting with the fact that many of the rules we follow about good programming practice are wrong…

Writing Unit Tests means Breaking the Rules

OK, so maybe that should be “incomplete” rather than wrong.

As well as general “golden rules” that govern good programming practice, each language will have it’s own specific rules. These rules are usually along the lines of “Never do x” or “Always do Y”.
Leaving aside the problems inherent of using the words “Always” and “Never” in this context, I am now of the opinion that they should normally end with the words “…unless you’re writing a Unit Test”.

Reviewing the test code I’ve written over the last few months offers numerous examples of this.
Of course, it could just be down to the quality of the programmer but…

Always use Bind Variables

This is a standard in PL/SQL for very good reasons. Bind variables not only offer significant performance advantages, they serve to protect against the injection of malicious code. However, whilst I found myself rather uncomfortable about writing this code in Ruby-plsql-spec…

def get_tournament_rec( i_comp_code, i_year_completed, i_host_nation)
# Return the record for a tournament
# Alternative version concatenating arguments into a string...
    l_stmnt = "
        select id, comp_code, year_completed, host_nation, year_started, number_of_teams
        from footie.tournaments
        where comp_code = '#{i_comp_code}'
        and year_completed = #{i_year_completed}
        and host_nation "
    if i_host_nation.nil? then
        l_stmnt = l_stmnt + "is null"
        l_stmnt += " = '#{i_host_nation}'"
        plsql.select_first l_stmnt

…this equivalent example using SQLDeveloper Unit Testing seemed perfectly fine…

select null
from competitions
where comp_code = '{I_CODE}'

…even in utPLSQL, we find stuff like …

procedure ut_update_no_of_teams
	-- Execute
	footie.manage_tournaments.edit_tournament(i_id => g_id, i_teams => 16, i_year_start => null);
	-- Validate
		'Confirm Number of Teams Updated',
		'select count(*) from footie.tournaments where id = '||g_id||' and number_of_teams = 16 and year_started is null',
end ut_update_no_of_teams;

Seeing these constructs in Application code would start alarm bells ringing. So why then, are they apparently OK in Unit Tests ?

Well, first of all, performance is not necessarily as crucial an issue for Unit Tests as it might be for the Application itself. You may well be able to live with the odd additional hard-parse in your test suite if it means writing a bit less test code.

From a security perspective, whilst it’s still prudent to be wary of concatenating user input into executable statements, in all of the above instances, the variables in question do not contain user supplied values. They are either generated at runtime or are hard-coded.

Wait, that’s not right is it ? I mean you’re supposed to avoid hard-coding values right ?

Do not hard-code values

The fundamental purpose of a unit test could be summarised as :

With an application in a known state, a known input will result in a known output.

That’s an awful lot of stuff that you need to know in order to execute a Unit Test and to then verify the output.
With this in mind, it’s not surprising that this sort of thing becomes commonplace in Unit Test Code :

create or replace package body  utp_footie.ut_edit_tournament 
	g_code footie.competitions.comp_code%type := 'EURO';
	g_year_end footie.tournaments.year_completed%type := 2016;
	g_host footie.tournaments.host_nation%type := 'FRANCE';
	g_teams footie.tournaments.number_of_teams%type := '24';
	g_year_start footie.tournaments.year_started%type := 2013;

Perhaps even more controversial in PL/SQL circles is code that contravenes the rule that says…

A When Others Exception not followed by a RAISE is a bug

In testing terms, my evidence for having this commandment re-worded is…

    procedure ut_add_tourn_start_after_end
        l_err_code number := 0;
        l_year_start footie.tournaments.year_started%type := 1918;
                i_code => g_code,
                i_year_end => g_year_end,
                i_teams => g_teams,
                i_year_start => l_year_start
        exception when others then
            l_err_code := sqlcode;
        utAssert.this('Cannot add a tournament that ends before it starts', l_err_code = -20000);
    end ut_add_tourn_start_after_end;

In this instance of course, we want to compare the error we actually get with the (hard-coded) error we were expecting.

One more illustration of exactly why a healthy disregard for rules is an asset when writing unit tests…

Any database interaction should be via a PL/SQL API

This is an approach that I’m particularly fond of, having had rather too much experience of applications where this architecture was not followed. However, if you’re writing a test for your PL/SQL API in a language that isn’t PL/SQL then something like this seems to be perfectly reasonable :

    expect( :all, "order by comp_code")
    ).to eq @expected

Now I’ve got that out of the way, it’s time to compare the frameworks in detail, starting with :

What these frameworks have in common

The first thing you may notice is that they are all similarly priced. That is to say that they are all free.
Both utPLSQL and Ruby-plsql-spec are Open Source.
SQLDeveloper, of which SQLDeveloper Unit Testing is an integral part, is also available at no cost.

As the first framework to be developed for PL/SQL, it is perhaps not surprising that utPLSQL has provided a template which the other frameworks have followed. This template itself, originated from JUnit.

In simple terms, a Unit Test consists of up to four phases :

  • Setup – any steps necessary to ensure that the Application is in a known state
  • Execute – run the code that is being tested
  • Validate – check that the actual results were what was expected
  • Teardown – any steps necessary to return the Application to it’s original state prior to the test being run

In terms of their capabilities, all of the Frameworks facilitate testing of scenarios that are commonly found in PL/SQL applications :

  • ability to test DML actions against a data model fully implementing Referential Integrity
  • ability to test DDL statements
  • ability to handle both OLTP and Data Warehouse style operations – including test steps that cross transaction boundaries
  • Ability to handle IN/OUT Ref Cursors

Additionally, they also share characteristics such as :

  • ability to run tests singly or as a larger suite
  • tests can be saved into discrete files and are therefore amenable to being stored in a Source Code Repository
  • possible (with varying degrees of configuration) to incorporate tests into a Continuous Integration tool

In short, any of the frameworks I’ve covered here will do the basics when it comes to Unit Testing your PL/SQL code. Their main distinguishing characteristics lie in their architecture…

SQLDeveloper Unit Testing

SQLDeveloper Unit Testing (SUT) is at the pointy-and-clicky end of the scale.
Typically for a declarative tool, there’s a bit of typing to start with but this reduces quickly once you start to add code to the Library.
Whilst SUT does require a database to house it’s repository objects, the fact that it’s built-in to SQLDeveloper means that the repository objects (and the tests themselves) can be separated completely from the database that holds the code to be tested.
The tests can be saved into text files (xml) and therefore placed under source control like any other text file.
If you want to execute SUT tests in a Continuous Integration environment, that environment will need to have SQLDeveloper installed. The tests themselves, can be executed using the SQLDeveloper CLI.

SUT is likely to be appealing as the PL/SQL testing framework of choice if :

  • SQLDeveloper is your standard Oracle IDE
  • You want to get up and running quickly with your TDD effort
  • You want to maintain a separation between test and application code in the RDBMS


Unsurprisingly, utPLSQL is at the opposite end of the spectrum.
As with SUT, the repository lives in the database. However, the actual tests themselves are PL/SQL packages and are therefore likely to exist right alongside the application code being tested.
Of course, it’s perfectly possible to maintain some distinction and ease administration by ensuring that the tests are located in a separate schema.
Issues of Source Control and CI don’t really arise as the infrastructure required for utPLSQL is the same for that of the PL/SQL application code that you’re testing.

utPLSQL may come up for consideration because :

  • it is (probably) the most widely used PL/SQL testing framework
  • it has no dependency on any software that you don’t already have up and running
  • it provides a greater degree of control than SUT as all tests are pretty much hand-written
  • SQLDeveloper is not your default IDE


Ruby has long been a favourite language when writing unit tests. The RSpec framework, upon which ruby-plsql-spec is based is highly regarded by many who make their living writing code outside of the database.
Unlike the other two frameworks, ruby-plsql-spec does not need to create any objects in any Oracle database.
All it needs is a means of connecting to the database holding the application code you want to test.
In terms of pre-requisites for executing tests in a CI envioronment, all you need is ruby itself ( which comes as standard in most Linux Distros), and the Ruby Gems that we installed for the client.

Ruby-plsql-spec is a contender when :

  • You’re already testing other technologies in your application using Ruby
  • You want a complete separation between test and application code
  • SQLDeveloper is not your default IDE
  • The overhead of writing more code is offset by either/both of the above
  • You’re happy to sacrifice some of the flexibility offered by the fact that the other frameworks are native to Oracle ( e.g. Merge statements)

It’s clear that each of these frameworks have their own particular strengths and weaknesses, but which one would I choose ?

If the Fence is Strong Enough, I’ll Sit on It

If you ask me which of these frameworks I’ll use going forward, the answer is all of them.

For my Footie application, which served as a Guinea Pig for the framework evaluation, I’m going to stick with SUT.
I use SQLDeveloper and using the declarative tools mean that I’ll spend much less time coding tests and more time doing the fun stuff.

As far as utPLSQL is concerned, not only is it highly likely that I’ll find myself working on a project where this is used as the standard, there is a major overhaul under way to bring utPLSQL up to date.
utPLSQL Version 3.0 may well be a game-changer.

As for Ruby-plsql-spec, that’s the framework that we use for the crudo application. I’m in the process of adding more tests using this framework ( or will be when I finish writing this).


Whatever approach to Unit Testing and/or Test Driven Development that appeals, there is a framework freely available for PL/SQL.
Whilst introducing tests to an existing code base can be challenging, adopting one of these a the start of a new project could well lead to a saving in time and effort down the line.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.