Testing Times – using ruby-plsql-spec for testing PL/SQL

There is method in the madness. It’s now clear that Donald Trump’s reluctance to commit to the Paris Climate Change Accord is because US methane emissions have been hugely under estimated. Yes, it turns out that there are many more Shy Trumpers in America than (almost) anyone expected.
Meanwhile, back in the UK we know that Brexit means Brexit but we still don’t know what Brexit means.
In amongst the chaos, UKIP have decided to take a fresh approach to the business of selecting a leader. This time, they’re staging a Cage Match.

Taking a leaf out of UKIP’s book I’ve decided to take a slightly unusual approach to Unit Testing my PL/SQL code.
Having looked at the SQLDeveloper Unit Testing Tool and utPLSQL, both of which utilise the database to persist objects, this time, I’m taking a look at a framework which takes a rather less database-centric approach, namely ruby-plsql-spec.

What I’ll be looking at is :

• Installation of the framework and required components
• A quick recap of the application being tested
• Writing and executing Unit Tests
• Summary and conclusions

As usual, I’ll be using Oracle 11gXE as my Oracle database. As for the Operating System, I’m running on the Debian (via Ubuntu) – based Linux Mint 17.3 (64-bit).
The Ruby version I’ll be using is 1.9.3. Before I dive in though…

A word on ruby-plsql-spec

This framework is an amalgamation of ruby-plsql – a library providing OCI interaction with Oracle, and RSpec – a Ruby Testing Library.

As you are about to discover, I’m not a Ruby programmer.
Stylistic considerations here are limited to :

• indentation is 4 spaces
• Variables declared globally in a program are prefixed g_
• Variables local to a function are prefixed l_
• Input parameters to a Ruby function are prefixed i_

In an effort to stave off a visit from the Ruby Style Police I have at least configured Geany with a trendy dark theme…

I’ve also done a bit of background reading and found the following to be rather helpful :

• Jacek Gebal has produced a whole series of articles on the ruby-plsql-spec framework
• He’s also produced a very handy ruby-plsql cheat sheet
• The author of the framework, Raymond Simanovskis has written several articles on this subject, starting with this one

First up then…

Installation

There are a couple of pre-requisites for installing ruby-plsql-spec.
You need to have Ruby itself installed. According to the documentation the supported versions are Ruby 1.8.7, 1.9.3 or 2.x.
You also need an Oracle Client.

You can find instructions for installing ruby-plsql-spec on Windows in the project documentation on GitHub.
In my case, I also referenced the official Ruby installation instructions.
As I’m currently running on a Linux Debian based system ( Mint), what follows are instructions for that platform. These should work on most Debian based distros ( e.g. Ubuntu).

NOTE – Instructions for installing an Oracle Client on a Debian system can be found here.

As we’re running on Linux, it’s likely that we already have a version of ruby installed.
You can check this, not to mention determine the Ruby version, by running :

ruby --version

Following the official Ruby Installation instructions, I refreshed Ruby by running :

sudo apt-get install ruby-full

I also found it necessary to install the zlib1g-dev package :

sudo apt-get install zlib1g-dev

Once this had completed, I was then able to install the ruby-plsql-spec gem :

sudo gem install ruby-plsql-spec

which produced the following output…

Building native extensions.  This could take a while...
Fetching: ruby-plsql-spec-0.5.0.gem (100%)
Successfully installed nokogiri-1.6.8.1
Successfully installed ruby-plsql-spec-0.5.0
2 gems installed
Installing ri documentation for nokogiri-1.6.8.1...
Installing ri documentation for ruby-plsql-spec-0.5.0...
Installing RDoc documentation for nokogiri-1.6.8.1...
Installing RDoc documentation for ruby-plsql-spec-0.5.0...

Installation of the ruby-oci8 gem was a bit more entertaining….

sudo env ORACLE_HOME=$ORACLE_HOME /usr/bin/env LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/xe/lib: /usr/bin/gem install ruby-oci8

NOTE – I needed to explicitly specify the LD_LIBRARY_PATH value because, as I’m using the Oracle Express Edition database on my system, I haven’t got an Oracle Client installed per se and therefore do not have a $LD_LIBRARY_PATH environment varaible set.
If you are using a conventional Oracle Client installation, then you can just specify $LD_LIBRARY_PATH in the above command.

Anyway, the output should be something like :

Building native extensions.  This could take a while...
Successfully installed ruby-oci8-2.2.2
1 gem installed
Installing ri documentation for ruby-oci8-2.2.2...
Installing RDoc documentation for ruby-oci8-2.2.2...

Setting up a Test Project

To initialise ruby-plsql-spec for your project, go to the base directory of the project and run :

plsql-spec init

This should result in something like :

      create  spec/factories
      create  .rspec
       exist  spec
      create  spec/database.yml
      create  spec/helpers/inspect_helpers.rb
      create  spec/helpers/time_helpers.rb
      create  spec/spec_helper.rb

Please update spec/database.yml file and specify your database connection parameters.

Create tests in spec/ directory (or in subdirectories of it) in *_spec.rb files.

Run created tests with "plsql-spec run".
Run tests with "plsql-spec run --coverage" to generate code coverage report in coverage/ directory.
Run tests with "plsql-spec run --html" to generate RSpec report to test-results.html file.

The final step is to edit the database.yml file that’s been generated in the spec sub-directory under the project root directory ( i.e. where you just ran the init command), to set the appropriate credentials to connect to the database.

Yes, there probably is a more secure way of configuring this, but it will do for the purposes of the current demonstration.

The initial version of the database.yml file looks like this :

# Change default connection username, password and database.
# Specify also host and port if not using tnsnames.ora connection.
default:
  username: hr
  password: hr
  database: xe
  # host: localhost
  # port: 1521

# Add other connection if needed.
# You can access them with plsql(:other) where :other is connection name specified below.

# other:
#   username: scott
#   password: tiger
#   database: xe

As I’m using a database called xe and will be connecting as the user FOOTIE, I’ve edited the default connection in the file to be :

default:
  username: footie
  password: the_password_for_footie
  database: xe

…where the_password_for_footie is the database password for the FOOTIE user.

After all of that, I suppose we’d better test that everything is working as expected, whilst at the same time starting to explore this strange new framework.

An Example test Program

In order to take a first look at just how ruby-plsql-spec works, I’ve put together a simple test program which :

• defines a global variable
• defines a function which is called from other functions in the program
• illustrates a method of calling a stored program unit using the ruby-plsql API
• perform one-time setup activities before executing any tests
• peforms a setup activity before each test
• uses the ruby-plsql API to perform various DML activities on a database table

I’ve commented the program extensively so hopefully it all makes sense when you read through it (or possibly, when you come to refer back to it becuase you’re trying to write something else).
I’ve called the program dml_examples.rb and saved it in the spec directory created when the project was initialized :

describe "ruby-plsql-spec DML examples" do
    # Global variables
    g_message = 'Default Message'


    # "Global" Functions
    def get_db_session
        #Function to retrieve the audsid for the current database session
        # Generally, a function returns the output of the last operation.
        # Note - this is an example of using the ruby-plsql API to call a database stored program unit
        plsql.sys_context('userenv', 'sessionid')
    end

        
    before(:all) do
        #
        # The first block of code that gets executed when the program runs
        #

        # Start by calling our global function and displaying the session id it returns...
        l_db_sess = get_db_session
        print "Before All. Session Id = #{l_db_sess}\n"
        
        # Create the test table (if it doesn't already exist)
        # In this instance, the SQL statement is assigned to a string variable which is then passed to
        # the API...

        l_table_ddl = "create table mytest( id number, message varchar2(100))"

        # The rescue nil clause essentially ignores ORA-00955 raised if the table already exists
        plsql.execute l_table_ddl rescue nil
    end

    before(:each) do
        # This gets executed before every other code block in the program...
        # but after the before(:all) block...
        # Note - issuing a rollback to an unnamed savepoint causes ruby-plsql to complain
        # so...
        plsql.savepoint "saveit"
    end


    it "should create a record in mytest" do
        # Insert records into the test table

        #declare a local variable
        l_id = 1

        #...create a hash table of the row we want to insert...
        l_testrec = { :id => l_id, :message => g_message }
        # ...and insert it into the table
        plsql.mytest.insert l_testrec

        # Use string concatenation to build a SQL query
        l_check_stmnt = "select count(1) from mytest where id = #{l_id} and message = '#{g_message}'"
        # Then pass the query to the plsql API.
        # Wrap that call in an assertion (expect) to test that result.
        expect(plsql.select_one(l_check_stmnt)).to eq 1
    end

    it "should upate a record in mytest" do
        l_message = 'New Message'
        l_id = 1
        # Setup using scalar variables this time..
        plsql.mytest.insert( :id => l_id, :message => g_message)
        # Execute
        plsql.mytest.update( :message => l_message, :where => {:id => l_id})

        # Validate
        # This time we're binding variables into the statement...
        l_count = plsql.select_one <<-SQL, l_id, l_message
            select count(1)
            from mytest
            where id = :l_id
            and message = :l_message
        SQL
        expect( l_count).to eq 1
     end

     it "should delete a record from the table" do
        # Setup - assign a mixture of variables and literals to a hash
        l_testrec = { :id => 2, :message => g_message }
        plsql.mytest.insert l_testrec
        # Delete that record...
        plsql.mytest.delete( :id => 2)
        l_count = plsql.select_one <<-SQL
            select count(1)
            from mytest
            where id = 2
        SQL
        expect( l_count).to eq 0
    end

    it "should select records from the table" do
        # Create an array of hashes
        l_testrecs =
        [
            { :id => 1, :message => g_message},
            { :id => 2, :message => g_message},
            { :id => 3, :message => g_message}
        ]
        # to insert into the table
        plsql.mytest.insert l_testrecs
        # And check that the contents of the table matches the hash array
        expect( plsql.mytest.all).to eq l_testrecs
    end

    after(:each) do
        # Rollback any DML issued as part of the test
        plsql.rollback_to "saveit"
    end

    after(:all) do
        # The last thing to run before the program terminates
        l_db_sess = get_db_session
        print "After All. Session = #{l_db_sess}\n"
        plsql.execute <<-SQL
            drop table mytest
        SQL
    end
end

Normally, ruby-plsql-spec requires that you start in the project root directory and then issue the run command. By default, it will then run any program in the tree under the spec sub-directory which has a name matching the pattern *_spec.rb.

In this case, we only want to run one program ( which is not named using this convention).
Additionally we want a bit more feedback than we get by default so we want to tell ruby-plsql-spec to use it’s Document reporter.

The plsql-spec run command has quite a few options as you can see by running :

plsql-spec run --help

For now though, we can achieve what we need by running :

plsql-spec run -fd spec/dml_examples.rb

The result looks something like this :

This tells us that ruby-plsql-spec runs the tests in the order that they are defined in the program.
The other point worth noting is that the database session id returned at the end of the program is the same as at the start.
Therefore, it would appear that all of the database interactions in the program take place within a single database session.

The implicit before_each Savepoint

There is another aspect of the framework that is not immediately obvious from this program.
When you include a before each block in your test, the framework creates a “before_each” savepoint in the background. It also issues a rollback to this savepoint in a corresponding implicit after-each block.
As I’m not entirely sure how this “background” transaction control works, I’ve explicitly included code to do this in my tests. As we shall see, this implicit code may cause some confusion at times.
The code that implements the before_each savepoint can be found in spec/spec_helper.rb which is one of the files created when the project is initialized.

Now we’ve had an introduction to ruby-plsql-spec, we can start applying this framework to a PL/SQL application…

Folder Structure used for tests

Initially, I will be running my tests one-at-a-time until I get them working.
Ultimately however, I want to be able to run all of the tests in one go.
For this reason, I’ve adopted what appears to be the standard directory structure.

As part of initialization, the framework has already created the spec directory.

Under this directory, I’m going to create a directory called helper to hold any library routines that I write and need to reuse across multiple tests.
As for the tests themselves, I’m going to follow Jacek’s recommended approach by creating a sub-directory under spec for each PL/SQL package I write tests for.

If you prefer a picture :

As for the application we’re testing…

A quick re-cap of the Footie Application

As ever, we’ll be using the Footie application.

Essentially, the bits of the application we’re interested in are the data model :

The MANAGE_COMPETITIONS package…

create or replace package manage_competitions
as
    procedure add_competition
    (
        i_code competitions.comp_code%type,
        i_name competitions.comp_name%type,
        i_desc competitions.description%type default null
    );

    procedure remove_competition( i_code competitions.comp_code%type);

    procedure upload_competitions;

end manage_competitions;
/

…and the MANAGE_TOURNAMENTS package…

create or replace package manage_tournaments
as
    procedure add_tournament
    (
        i_code tournaments.comp_code%type,
        i_year_end tournaments.year_completed%type,
        i_teams tournaments.number_of_teams%type,
        i_host tournaments.host_nation%type default null,
        i_year_start tournaments.year_started%type default null
    );

    procedure remove_tournament( i_id tournaments.id%type);

    procedure edit_tournament
    (
        i_id tournaments.id%type,
        i_teams tournaments.number_of_teams%type,
        i_year_start tournaments.year_started%type default null
    );    

    procedure list_tournaments
    (
        i_comp_code tournaments.comp_code%type,
        io_tourn_list in out SYS_REFCURSOR
    );
end manage_tournaments;
/

First Tests – Adding a Competition

The Add a Competition story requires two tests.
The first is to ensure that we can add a new Competition record.
The second is to make sure that we can’t add a Competition record more than once.

The first cut of the test program looks like this :

describe "Create Competitions in the FOOTIE application" do
    #
    # First version of the add competition test
    #
    def get_new_comp_code
        # Return a value that can be used as a competition code but which
        # does not already exist in the application
        plsql.select_one <<-SQL
            with suffix as
            (
                select max( to_number( substr( comp_code, regexp_instr( comp_code, '[[:digit:]]')))) + 1 as numeral
                from footie.competitions
                where comp_code like 'UT%'
                and regexp_instr( substr( comp_code, -1, 1), '[[:digit:]]') = 1 -- only want codes with a numeric suffix
                union -- required if there are no records in the table
                select 1 from dual
            )
            select 'UT'||max(numeral)
            from suffix
            where numeral is not null
        SQL
    end
    

    def get_competition( i_comp_code)
        plsql.select <<-SQL, i_comp_code
            select comp_code, comp_name, description
            from footie.competitions
            where comp_code = :i_comp_code
        SQL
    end

    def ensure_competition_exists( i_comp_code)
        plsql.execute <<-SQL, i_comp_code
            merge into footie.competitions
            using dual
            on (comp_code = :i_comp_code)
            when not matched then
                insert( comp_code, comp_name, description)
                values( :i_comp_code, 'Test', 'A test')
        SQL
    end
    
    before(:each) do
        plsql.savepoint "add_comp_savepoint"
    end

    it "should add a new record to the COMPETITIONS table" do
        l_comp_code = get_new_comp_code
        l_comp_name = 'Test'
        l_comp_desc = 'A test'

        plsql.footie.manage_competitions.add_competition( l_comp_code, l_comp_name, l_comp_desc)

        expected_record = [ { :comp_code => l_comp_code, :comp_name => l_comp_name, :description => l_comp_desc } ]

        expect( get_competition( l_comp_code)).to eq expected_record
    end

    it "should raise an error when we try to add a duplicate Competition" do
        l_comp_code = 'WC'
        ensure_competition_exists( l_comp_code)

        # This time we're looking for an Oracle Error, which we'll pick out from the error stack.
        # Note the curly brackets for the expect call...
        expect{
            plsql.footie.manage_competitions.add_competition( l_comp_code, 'Test')
        }.to raise_error(/ORA-00001/)
    end

    after(:each) do
        plsql.rollback_to "add_comp_savepoint"
    end
end

The first of these tests requires us to use techniques that we’ve already seen in the DML examples.
The second however is a bit different.
For a start, the call to the expect function is enclosed in curly brackets rather than round ones.
Secondly, we have a new operand – “to_raise_error”.

As far as I can work out, the curly brackets are required because we’re getting the error stack back from our call to the packaged procedure.

A feature common to all of the test frameworks we’ve looked at is that, at the moment, we have some functions which look as if they would be useful for other tests that we need to write, not just the ones we’re currently running.

As with the more Database-centric frameworks, ruby-plsql-spec will allow us to store these functions in a separate helper program…

Creating a Helper function

We need to move the relevant functions out of our Add Competitions test code, and into a separate program.

The new program is called footie_helpers.rb and is saved to the spec/helpers directory :

def get_new_comp_code
    # Return a value that can be used as a competition code but which
    # does not already exist in the application
    plsql.select_one <<-SQL
        with suffix as
        (
            select max( to_number( substr( comp_code, regexp_instr( comp_code, '[[:digit:]]')))) + 1 as numeral
            from footie.competitions
            where comp_code like 'UT%'
            and regexp_instr( substr( comp_code, -1, 1), '[[:digit:]]') = 1 -- only want codes with a numeric suffix
            union -- required if there are no records in the table
            select 1 from dual
        )
        select 'UT'||max(numeral)
        from suffix
        where numeral is not null
    SQL
end

def get_competition( i_comp_code)
    # Return a competition record for the given COMP_CODE
    plsql.select <<-SQL, i_comp_code
        select comp_code, comp_name, description
        from footie.competitions
        where comp_code = :i_comp_code
    SQL
end

def ensure_competition_exists( i_comp_code)
    # If a competition with I_COMP_CODE does not already exist, create it.
    plsql.execute <<-SQL, i_comp_code
        merge into footie.competitions
        using dual
        on (comp_code = :i_comp_code)
        when not matched then
            insert( comp_code, comp_name, description)
            values( :i_comp_code, 'Test', 'A test')
    SQL 
end

Yes, that code does look rather familiar. It’s all of the “global” functions that we’re in our add_competitions_spec.rb program. Now however, that program has gone on something of a diet…

 
describe "Create Competitions in the FOOTIE application" do
    
    before(:each) do
        plsql.savepoint "add_comp_savepoint"
    end

    it "should add a new record to the COMPETITIONS table" do
        l_comp_code = get_new_comp_code
        l_comp_name = 'Test'
        l_comp_desc = 'A test'

        plsql.footie.manage_competitions.add_competition( l_comp_code, l_comp_name, l_comp_desc)

        expected_record = [ { :comp_code => l_comp_code, :comp_name => l_comp_name, :description => l_comp_desc } ]

        expect( get_competition( l_comp_code)).to eq expected_record
    end

    it "should raise an error when we try to add a duplicate Competition" do
        l_comp_code = 'WC'
        ensure_competition_exists( l_comp_code)

        # This time we're looking for an Oracle Error, which we'll pick out from the error stack.
        # Note the curly brackets for the expect call...
        expect{
            plsql.footie.manage_competitions.add_competition( l_comp_code, 'Test')
        }.to raise_error(/ORA-00001/)
    end

    after(:each) do
        plsql.rollback_to "add_comp_savepoint"
    end    
end

The good bit is that the functions are still referenced in exactly the same way and the tests still run successfully…

plsql-spec run -fd spec/manage_competitions/add_competitions_spec.rb

…returns…

Messing about with nulls

Sooner or later, you’re likely to have to deal with null values.
The FOOTIE application is no exception, especially when it comes to TOURNAMENT records.

The TOURNAMENT table has a unique key comprising comp_code, year_completed and host_nation.
The twist here is that a Tournament does not necessarily need to have a host.
The Edit Tournament story requires tests to

• edit the number of teams for a tournament
• edit the year that a tournament started
• unset the year that a tournament started

When we come to write these tests we can explore just how ruby-plsql-spec handles null values in various contexts.

First of all, we’ll need a helper function to return a tournament record for a given comp_code/year_started/host_nation combination.
Now, attempting to bind a ruby variable with a null value into an SQL statement will cause complaint at runtime.
Therefore, we need to check to see if a variable is null (nil in ruby) before binding…

...
    def get_tournament_rec( i_comp_code, i_year_completed, i_host_nation)
    # Return the record for a tournament
        if i_host_nation.nil? then
            plsql.select_first <<-SQL, i_comp_code, i_year_completed
                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 is null
            SQL
        else
            plsql.select_first <<-SQL, i_comp_code, i_year_completed, i_host_nation
                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 = :i_host_nation
            SQL
        end
    end
... 

I’m using plsql.select_first here, just to demonstrate that this will return an array consisting of a single record, rather than a hash. It’s worth pausing, just for a moment, to ask what may well be a pertinent question – namely – why did I write this function to use bind variables rather than writing a shorter version that simply concatenated variables into a string ? Now, if we were writing Application Code, then the answer would be fairly obvious – i.e. bind-variables promote soft parsing and protect against injection. However, the rules for writing unit tests appear to be somewhat different. After all, it seems that I’ve been quite happy to use this technique in both of the other testing frameworks I’ve looked at so far. This is a topic that I will come back to in a future post. For now though, here’s an example of the get_tournament_rec function that uses the “string concatenation method” :

...
    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"
        else
            l_stmnt += " = '#{i_host_nation}'"
        end
            plsql.select_first l_stmnt
    end
...

As part of the setup phase for our edit tests, we need to make sure that we have a tournament record to edit. As ruby-plsql does not currently support the MERGE statement natively, and plsql.execute seems to have an issue when you attempt to bind more than two variables, the helper function we’re going to write for this task will need to check to see if the tournament record exists and then create it if it doesn’t. To begin with then, it needs to call the function we’ve just written and check to see if it returns NULL (nil)…

...
    def ensure_tournament_exists( i_comp_code, i_year_completed, i_number_of_teams, i_host_nation)
        # Make sure that there is a TOURNAMENT record for this comp_code, year_completed, host_nation combination.
        # Number of teams is optional.
        
        # Make sure that there is a parent COMPETITIONS record for the Tournament
        ensure_competition_exists( i_comp_code)
        # Normally, we'd just do a merge here. However, passing more than two variables to
        # plsql.execute for binding causes an error. Therefore, we either need to concatenate the variables into a
        # string, or do what we did in the days before the MERGE statement came along...
        if get_tournament_rec( i_comp_code, i_year_completed, i_host_nation).nil? then
            new_tournament = 
            {
                :id => plsql.footie.tourn_id_seq.nextval,
                :comp_code => i_comp_code,
                :year_completed => i_year_completed,
                :host_nation => i_host_nation,
                :number_of_teams => i_number_of_teams
            }
            plsql.footie.tournaments.insert new_tournament
        end
    end
...

With these functions added to our helper program ( or a new program in the helper directory if you prefer), we can write our tests.

Note that I’ve “tweaked” the MANAGE_TOURNAMENTS.EDIT_TOURNAMENT procedure for the purposes of this example so that it now looks like this :

...
    procedure edit_tournament
    (
        i_id tournaments.id%type,
        i_teams tournaments.number_of_teams%type,
		i_year_start tournaments.year_started%type default null
    )
    is
        l_year_end tournaments.year_completed%type;
    begin
        if i_year_start is null and i_teams is null then
            -- Just set year_started to null.
            update tournaments
            set year_started = null
            where id = i_id;
            return;
        end if;
        if i_year_start is not null then
            select year_completed into l_year_end
            from tournaments
            where id = i_id;
            if not end_after_start( i_year_start, l_year_end) then
                raise_application_error( -20000, q'[A tournament cannot end before it has begun...unless you're England !]');
            end if;
        end if;
        update tournaments
        set number_of_teams = nvl(i_teams, number_of_teams),
            year_started = nvl(i_year_start, year_started)
        where id = i_id;
    end edit_tournament;
...

The tests look like this :

describe "Edit Tournament records in the FOOTIE application" do

    # CEIC - Central European International Cup 1927-30 contested by 5 teams
    
    g_comp_code = 'CEIC'
    g_year_completed = 1930
    g_host_nation = nil
    g_number_of_teams = 1
            
    before(:all) do
        # Setup a tournament record to peform updates against
        ensure_tournament_exists( g_comp_code, g_year_completed, g_number_of_teams, g_host_nation)
    end

    before(:each) do
        plsql.savepoint "edit_tourn_savepoint"
    end

    it "should update the number of teams in a tournament" do
        l_teams = 5
        # The first element of the result array holds the ID value
        l_tourn_id = get_tournament_rec( g_comp_code, g_year_completed, g_host_nation)[0]
        # Execute
        #i_year_start is an optional parameter in the proc so we don't have to pass it...
        plsql.footie.manage_tournaments.edit_tournament(:i_id => l_tourn_id, :i_teams => l_teams)
        # Validate - number_of_teams is the 6th element in the results array
        l_actual_teams = get_tournament_rec( g_comp_code, g_year_completed, g_host_nation)[5]
        expect( l_actual_teams).to eq l_teams
    end

    it "should update the Year that a tournament started" do
        l_year_started = 1927
        l_tourn_id = get_tournament_rec( g_comp_code, g_year_completed, g_host_nation)[0]
        # Execute
        # i_teams is a mandatory parameter so we have to pass it NULL
        plsql.footie.manage_tournaments.edit_tournament( :i_id => l_tourn_id, :i_year_start => l_year_started, :i_teams => NULL)
        # Validate - year_started is the 5th element in the result array
        expect( get_tournament_rec( g_comp_code, g_year_completed, g_host_nation)[4]).to eq l_year_started
    end

    it "should set the Year that a tournament started to null" do
        # Setup - make sure tournament has a not null YEAR_STARTED value
        l_tourn_id = get_tournament_rec( g_comp_code, g_year_completed, g_host_nation)[0]
        l_year_started = 1927
        plsql.footie.tournaments.update( :year_started => l_year_started, :where => {:id => l_tourn_id})
        # Execute - set the YEAR_STARTED to null
        plsql.footie.manage_tournaments.edit_tournament( :i_id => l_tourn_id, :i_year_start => nil, :i_teams => nil)
        expect( get_tournament_rec( g_comp_code, g_year_completed, g_host_nation)[4]).to be_nil
    end

    after(:each) do
        plsql.rollback_to "edit_tourn_savepoint"
    end
end

One major point to note here is that the ruby “nil” is aliased to “NULL” in the inspect_helpers.rb program.
This means that “nil” and “NULL” can be used interchangably. However, this is case sensitive. If you use “null” instead of “NULL”, you’ll find this out the fun way.
The other thing to note is the operator we’re using in the NULL comparison in our last test – i.e.

expect( get_tournament_rec( g_comp_code, g_year_completed, g_host_nation)[4]).to be_nil

If you wanted to reverse the comparison – i.e. check a value was not null, the syntax would be…

expect( my_value).not_to be_nil

Testing the contents of an IN/OUT Refcursor

Once again, we come to the fun bit. We want to test MANAGE_TOURNAMENTS.LIST_TOURNAMENTS which has the following signature :

...
    procedure list_tournaments
    (
        i_comp_code tournaments.comp_code%type,
        io_tourn_list in out SYS_REFCURSOR
    );
...

Whilst ruby-plsql-spec does handle Ref Cursors as a return value from a database function, a Ref Cursor as an IN/OUT parameter of a stored procedure is a bit problematic.
Of course, there is a case for re-implementing this functionality as a Function in the package instead of a procedure. However, one of my evaluation criteria for a Test Framework is that it must not necessitate changes to the code base of the application it’s being used to test.
That means that we need a bit of a fudge.
The steps in our test need to be :

1. create a table to hold the data we get back from the ref cursor
2. execute a PL/SQL block to read the ref cursor and write it’s contents to the table we’ve created
3. Validate by comparing the expected result set against what is in our table rather than directly against a Ref Cursor

So, having added the following function to footie_helpers.rb…

...
    def get_tournaments_for_competition( p_comp_code)
        plsql.select <<-SQL, p_comp_code
            select id, comp_code, year_completed, host_nation, year_started, number_of_teams
            from footie.tournaments
            where comp_code = :p_comp_code
        SQL
    end
...

…the test program that will do the job is called list_tournaments_spec.rb. looks something like this( suitably commented)…

describe "List Tournaments for a given Competition" do

    g_comp_code = 'WC'

    before(:all) do
        # We need to create a table to hold the result of the IN/OUT Ref Cursor.
        # As this is a DDL statement we'll do this first so we don't have any other operations
        # caught up in the same transaction
        plsql.execute "create table list_tournaments_output_tmp as
                select id, comp_code, year_completed, host_nation, year_started, number_of_teams
                from footie.tournaments
                where 1=2"
    end

    before(:each) do
        plsql.savepoint "list_tournaments_savepoint"
    end

    it "should list all of the tournaments for #{g_comp_code}" do
        # Now make sure we have some tournament records
        ensure_tournament_exists(g_comp_code, 1954, 16, 'SWITZERLAND')
        ensure_tournament_exists(g_comp_code, 1958, 16, 'SWEDEN')
        ensure_tournament_exists(g_comp_code, 1962, 16, 'CHILE')

        # Get the expected results
        @expected = get_tournaments_for_competition( g_comp_code)

        # Execution Step...
        # We're on 11g remember, so any new whizzy stuff in 12c is still tantalisingly out of reach...
        plsql.execute <<-SQL, g_comp_code
            declare
                l_rc sys_refcursor;
                type typ_tournament is table of tournaments%rowtype index by pls_integer;
                tbl_tournament typ_tournament;
                l_idx pls_integer := 1;
            begin
                manage_tournaments.list_tournaments(:g_comp_code, l_rc);
                loop
                    fetch l_rc into tbl_tournament(l_idx);
                    l_idx := l_idx + 1;
                    exit when l_rc%notfound;
                end loop;
                forall i in 1..tbl_tournament.count
                    insert into list_tournaments_output_tmp values tbl_tournament(i);
            end;
        SQL

        # Validate by comparing the results table to the expected results hash array
        expect( plsql.footie.list_tournaments_output_tmp.select(:all, "order by year_completed")).to eq @expected
    end

    after(:each) do
        plsql.rollback_to "list_tournaments_savepoint"
    end

    after(:all) do
        plsql.execute "drop table footie.list_tournaments_output_tmp"
    end
end

…not ideal then but no worse than the other frameworks I’ve looked at to date.

Table Backup

Our next test involves uploading COMPETITIONS from a csv file via an external table.
A quick reminder – the file looks like this :

comp_code,comp_name,description
HIC,Home International Championship,British Home International Championship
CA,Copa America,Copa America (South American Championship until 1975)
OLY,Olympic Football Tournament,The Olympics
WC,World Cup,The FIFA World Cup
CEIC,Central European International Cup,Central European International Cup - a forerunner to the European Championships
EURO,European Championship,UEFA European Championship
HIC,Home International Championship,British Home International Championship

…the external table for it is defined like this :

create table competitions_xt
(
    comp_code varchar2(5),
    comp_name varchar2(50),
    description varchar2(4000)
)
    organization external
    (
        type oracle_loader
        default directory my_files
        access parameters
        (
            records delimited by newline
            skip 1
            fields terminated by ','
            badfile 'competitions.bad'
            logfile 'competitions.log'
            (
                comp_code char(5),
                comp_name char(50),
                description char(4000)
            )
        )
            location('competitions.csv')
    )
reject limit unlimited
/

The code for the procedure to load these records is :

...
    procedure upload_competitions
    is
    begin
        insert into competitions( comp_code, comp_name, description)
            select comp_code, comp_name, description
            from competitions_xt
            log errors reject limit unlimited;
    end upload_competitions;
...

As we’re using a LOG ERRORS clause, any inserts into the error table will be done in an Autonomous Transaction. Therefore, we’re not going to be able to use a simple rollback as the teardown process.
Instead, what we need to do is to :

1. Create a backup of the data in the COMPETITIONS and ERR$_COMPETITIONS tables before the test is executed
2. Run the test and validate the results
3. Return the COMPETITIONS and ERR$_COMPETITIONS tables to the state they were in prior to test execution
4. Tidy up by removing the backups we made of the tables in step 1.

All of which can be accomplished using something like :

describe "Upload Competitions" do
    before(:all) do
        # Create copies of the current data in the target tables
        plsql.execute "create table competitions_bu as select rowid as bu_rowid, comp.* from footie.competitions comp"
        plsql.execute "create table err$_competitions_bu as select rowid as bu_rowid, err.* from footie.err$_competitions err"
    end

    it "should Upload new Competitions" do
        # Get expected results - in this case the expected contents of the Competitions table after the load.
        # Need to make sure the rows are in a known order, hence the in-line-view to allow the order by...
        @expected = plsql.select <<-SQL
            with all_rows as
            (
                select comp_code, comp_name, description
                from footie.competitions_xt
                where comp_code not in (select comp_code from footie.competitions)
                union
                select comp_code, comp_name, description
                from footie.competitions
            )
            select * from all_rows order by comp_code
        SQL
        # ...and again for the Error records...
        @expected_err = plsql.execute <<-SQL
            with all_errs as
            (
                select distinct comp_code, comp_name, description
                from footie.competitions_xt
                where comp_code in (select comp_code from footie.competitions)
                union
                select comp_code, comp_name, description
                from footie.err$_competitions
            )
            select * from all_errs order by comp_code
        SQL

        # ...and the error records. NOTE that plsql.select doesn't seem to like the table name for some reason, so...
        @actual_err = plsql.execute <<-SQL
            select * from footie.err$_competitions order by comp_code
        SQL

        # Execute
        plsql.footie.manage_competitions.upload_competitions

        # Validate - Loaded records first, then error records...
        expect( plsql.footie.competitions.select( :all, "order by comp_code")).to eq @expected
        expect( @expected_err).to eq @actual_err
    end

    after(:each) do

        # Restore the tables to their former state and cleardown the backup tables.
        # plsql.delete doesn't like sub-queries so...
        plsql.execute "delete from footie.competitions where rowid not in (select bu_rowid from competitions_bu)"
        plsql.execute "delete from footie.err$_competitions where rowid not in (select bu_rowid from err$_competitions_bu)"
        plsql.execute "truncate table competitions_bu"
        plsql.execute "truncate table err$_competitions_bu"
        # By this point we've invalidated the original savepoint created implicitly by the framework, so re-create it here...
        plsql.savepoint "before_each"
    end

    after(:all) do
        # Drop the backup tables
        plsql.execute "drop table competitions_bu"
        plsql.execute "drop table err$_competitions_bu"
    end
end

Note that, due to the fact we perform various DDL statements during the test executions, causing transactions to terminate. Therefore, the “implicit” before-each savepoint becomes invalid by the time ruby-plsql-spec tries to reference it. Therefore, we need to re-create it at the end of the after each function.

Running a “Suite” of tests

Up to this point, we’ve run our tests one at a time.
Whilst ruby-plsql-spec does not have the concept of a Suite of tests, per se, the way we’ve arranged our tests on disk, with tests for each package in it’s own sub-directory, means that we do have a de-facto test suite for each package.
For example, if we want to run all of the tests for MANAGE_COMPETITIONS, we simply need to run…

plsql-spec run -fd spec/manage_competitions/

…and we can see all of the tests for this package execute…

We can execute all of our tests for all packages by running the following from the project “root” directory :

plsql-spec run -fd

Outputting Test Results

As you can see from the help, there are four format options when running ruby-plsql-spec tests.
As well as the documentation format that I’ve used throughout this post, I found the html format may be of some interest…

plsql-spec run -fh >run_all.htm

Running this command generates a file called run_all.htm which, in a browser, looks like this :

If you want something a bit more basic – i.e. whether the build is Green (all tests pass) or Red (one or more tests fail) then something like this will do the job :

#!/bin/sh
plsql-spec run
([ $? == 0 ] && echo 'Build is Green' || echo 'Build is Red' )

When I run this I get…

 . ./testit.sh
Running all specs from spec/
.......

Finished in 1.03 seconds (files took 0.81997 seconds to load)
7 examples, 0 failures

Build is Green

At this point, I should really take things a step further and explore how we can run our tests using a Continuous Integration tool.
Fortunately, Jacek has already done this with Jenkins.

Conclusion

I must confess that I found ruby-plsql-spec to be rather different from the other two frameworks I’ve looked at.
Functionally, it does everything it needs to and the code required for me to write my tests is pretty compact.
However, it did feel slightly odd using programming constructs that I would find problematic in application code. For example, direct DML against tables using ruby-plsql rather than calling a PL/SQL program unit to handle it is something that goes against the principle of Thick Database Design.
I have to say that this is no reflection on the framework, which I’ve grown to like in a short space of time. It’s rather to do with the nature of test code itself.
This is a topic that I will be looking at next when I’ll be reviewing all of the frameworks that I’ve covered over the past few months and seeing how they compare with each other.