utPLSQL – We’re building the…Unit Tests!

You’ll be relieved to hear that I’m not going to talk about Brexit any more. It’s done and finished and it’s time everyone moved on.
Besides, from my current vantage point ( a small island off the coast of Europe), other people have their own problems.
In the USA for example, the nation is currently engrossed in what appears to be an exercise to find the second most unpopular person in the country…and make them President.
If the subject of this post were a Presidential Candidate, it would probably be nicknamed “The Comeback Kid”.
No, Bill Clinton hasn’t released a PL/SQL testing framework, but Steven Feuerstein has and – contrary to what you may have heard – utPLSQL is very much alive and kicking.
As the first Test framework written specifically for PL/SQL, utPLSQL is the perfect subject for my continuing testing odyssey.

To date, this has included

The political analogy is rather apposite when you consider that using utPLSQL largely revolves around making a number of assertions…which may or may not be true.

What we’ll be looking at is :

  • Installing the utPLSQL framework
  • Your first Test and how to run it
  • A quick recap of the Footie Application we’re testing with
  • The utPLSQL Assert API
  • Building and executing Test Suites
  • Retrieving utPLSQL test execution results programmatically

But first….

Testing Approach

Just a reminder from previous posts on this topic, I want to keep my tests separate from my Application Code. For utPLSQL this will require a separate database schema with appropriate privileges to hold and execute the tests.

Playing around with SQLDeveloper Unit Testing, I found that maintaining commonly used test routines in a library was very useful in improving productivity when it came to writing tests. Therefore, I’ll be implementing the equivalent for utPLSQL in the form of a helper package. this will also reside in our “test” schema.

Initially at least, my tests need to be written to support User Stories ( i.e. application functionality) rather than fitting in with the application programs ( packages and their members in this case).

The tests themselves need to be independently runnable – i.e. a test should not depend on the successful execution of a previous test to ensure successful execution of itself.

As utPLSQL has been developed over a period of time, the naming conventions it follows seemed to have moved a bit as well.
Whilst PL/SQL itself is case insensitive, I’ve used Camel Case where it is suggested by the object names in the framework. Where the naming is more traditional – using underscores as word separators, I’ll be sticking to the more usual PL/SQL conventions.

As far as the tests that I’ll be writing here, I’ll be adopting the approach of trying to use descriptive names for each of the tests. This contrasts with the approach I took with SQLDeveloper Unit Testing where the tests were largely numbered within the name of the User Story being tested.

It’s probably useful to understand the historical context in which utPLSQL first saw the light of day. That was in the year 2000.
For this reason you should not be entirely surprised to see references to Oracle 8i when rummaging through the utPLSQL source code.
Fortunately, as it is still actively maintained, it has managed to stave-off bit-rot and is still relevant on the 11g Express Edition database (that I’ll be using for my examples in this post) and beyond.

One final point – it’s very tempting to go off on a tangent and before you know it, you’ve written your own bespoke testing framework with utPLSQL at it’s core.
I’ve tried very hard to resist this temptation in the examples that follow.

Installing utPLSQL

Head over to the utPLSQL Home Page and click on the link to download the latest release (2.3.1 in my case).
Once the zip file has been downloaded, extract it into a directory of your choice.
You will find that the following sub-directories have been created :

  • Code – holds the framework source code
  • Examples – source code for the examples detailed in the documentation
  • Docs – the documentation – in HTML

Open the docs/index.html file in a web browser and follow the link to Getting Started and then Step1. Install utPLSQL…

Creating the utPLSQL owner

…from this point on my setup steps differ somewhat from those in the documentation.
The first thing I did was to create the UTP schema using the following code. Note that this needs to run as a user with permissions to grant execute on DBMS_PIPE and UTL_FILE ( i.e. something other than SYSTEM)…

spool utp_user.log
set verify off
define utp_owner = utp
define utp_default_ts = users
define utp_temp_ts = temp
accept passwd prompt 'Enter a password for the new schema [] : ' hide

prompt creating user

create user &utp_owner identified by &passwd
    default tablespace &utp_default_ts
    temporary tablespace &utp_temp_ts
/

set verify on
prompt Assigning tablespace quota

alter user &utp_owner quota unlimited on &utp_default_ts
/

prompt Granting System Privileges

grant create session, create table, create procedure,
  create sequence, create view, create public synonym,
  drop public synonym to &utp_owner
/

prompt Granting Object Privileges
--
-- Required to enable the following packages to compile :
--
-- ut_plsql
-- ut_filereporter
-- ut_assert2
-- ut_gen

grant execute on utl_file to &utp_owner
/

--
-- Require to enable the following package to compile :
-- ut_pipe
--
grant execute on dbms_pipe to &utp_owner
/

spool off

Run this and the resulting log file should look like this :

Enter a password for the new schema [] :
creating user

User created.

Assigning tablespace quota
old   1: alter user &utp_owner quota unlimited on &utp_default_ts
new   1: alter user utp quota unlimited on users

User altered.

Granting System Privileges
old   3:   drop public synonym to &utp_owner
new   3:   drop public synonym to utp

Grant succeeded.

Granting Object Privileges
old   1: grant execute on utl_file to &utp_owner
new   1: grant execute on utl_file to utp

Grant succeeded.

old   1: grant execute on dbms_pipe to &utp_owner
new   1: grant execute on dbms_pipe to utp

Grant succeeded.

Once that’s all sorted…

Installing utPLSQL

Navigate to the code directory that was created when you extracted the zip and connect as the new user (UTP in my case).

Now run :

@ut_i_do install

The output will be written to a file called utp_i_install.log in the current directory.

Assuming that everything has gone to plan, you will now be able to access the framework and it’s objects from any schema in the database.
At this point, you may consider it prudent to either revoke the PUBLIC SYNONYM privileges from the utPLSQL owning schema (UTP in my case), or simply lock it altogether. Why not do both…

revoke create public synonym from utp
/
revoke drop public synonym from utp
/

alter user utp account lock
/

Now, it’s time to take a look at :

Creating and Executing utPLSQL tests

The rules for utPLSQL tests are, pretty much that :

  • All tests must be contained in PL/SQL packages
  • a test package must include a procedure called ut_setup
  • a test package must include a procedure called ut_teardown
  • by default, a test package must start with the prefix UT_

If you choose to run your test by invoking the utPLSQL.test procedure, there may also be a requirement that the test package is named after the object to be tested e.g. in the format ut_package_name.

However, there is another procedure available which has virtually the same signature and implements more-or-less the same functionality but which allows us to break free of this test package naming restriction…

Creating your first test

Consider the following package, created in the utPLSQL owning schema (UTP in my case) which has no corresponding “Application package”.
Header first…

create or replace package ut_demo is
	procedure ut_setup;
	procedure ut_success;
	procedure ut_teardown;
end;
/

…and now the body…

create or replace package body ut_demo is
	procedure ut_setup
	is
	begin
		null;
	end ut_setup;

	procedure ut_success
	is
	begin
		utAssert.eq('This is what success looks like ', 1,1);
	end ut_success;	

	procedure ut_teardown
	is
	begin
		null;
	end ut_teardown;

end ut_demo;
/

As you can see, we have the mandatory package prefix.
We also have the required ut_startup and ut_teardown procedures which, in this case, don’t actually do anything.
As for the actual test, we’re using a call to the Assertion API – in this case the eq(uality) assertion to make sure that 1 is equal to 1.
Like all assertions in the framework, eq accepts a string to be displayed as a message with the test result as it’s first argument.

Running the test

To execute this, and see the results, this PL/SQL block should do the trick :

set serveroutput on size unlimited
begin
	-- Make sure that utPLSQL doesn't try to recompile the test package
	-- from a file in a directory object that we haven't specified
	utConfig.autocompile(false);
	-- and execute the test...
    utPLSQL.run( 'ut_demo');
end;
/

The serveroutput command is required in order to view the test results, which are displayed by utPLSQL via the medium of DBMS_OUTPUT.
The utConfig.autocompile setting is to tell utPLSQL not to look for the source of the package we’re running in a database directory object, because it’s not there.
From this test at least, it appears that utPLSQL likes to share good news :

success

The utPLSQL.run procedure gives us a few options when we execute tests…

Running startup and teardown for each test

One example of this can be seen when we tweak our UT_DEMO package a bit…

create or replace package ut_demo is
	procedure ut_setup;
	procedure ut_success;
	procedure ut_failure;
	procedure ut_teardown;
end;
/

create or replace package body ut_demo is
	procedure ut_setup
	is
	begin
		dbms_output.put_line('Executing UT_SETUP');
	end ut_setup;

	procedure ut_success
	is
	begin
		dbms_output.put_line('Executing UT_SUCCESS');
		utAssert.eq('This is what success looks like ', 1,1);
	end ut_success;	

	procedure ut_failure
	is
	begin
		dbms_output.put_line('Executing UT_FAILURE');
		utAssert.eq(q'[It's all gone horribly wrong]',1,2);
	end ut_failure;

	procedure ut_teardown
	is
	begin
		dbms_output.put_line('Executing UT_TEARDOWN');
	end ut_teardown;

end ut_demo;
/

…and re-execute…

set serveroutput on size unlimited
begin
    utPLSQL.run( 'ut_demo');
end;
/

setup_order

This tells us a few things about the framework.

By looking at the order our output messages are displayed, we can see that the ut_setup is executed first. This is then followed by each of our tests, in alphabetical order, rather than the order in which they appear in the package.
Finally, there is a single execution of ut_teardown, right at the end.

If you want to run the startup and teardown processes before and after each test, you can do this by calling utPLSQL.run like this :

set serveroutput on size unlimited
begin
    utPLSQL.run
    (
        testPackage_in => 'ut_demo',
        per_method_setup_in => true
    );
end;
/

By specifying the per_method_setup_in parameter as true, we can see that ut_setup is executed prior to and ut_teardown after, each of our tests :

sutd_each

I’m not sure how comfortable you should be with a test whose behaviour is dependent on how it’s called. If you are going to take advantage of this feature then you’ll probably want to consider whether you need to have a set run script for the test(s) in question.
Of course, you can explicitly call the ut_setup and ut_teardown procedures from each “test” procedure, thus ensuring that they are run before and after each test procedure.

Running a single test from a package

One other aspect of utPLSQL.run, which may come in handy when you’re developing a test, is the ability to run a subset of tests in a test package.
For example, if we just want to run the ut_success test in our package :

set serveroutput on size unlimited
begin
    utPLSQL.run
    (
        testPackage_in => 'ut_demo',
        subprogram_in => 'success'
    );
end;
/

There’s a couple of things to note about the subprogram_in paramater.

First of all, it accepts a wildcard, so this will also work :

begin
    utPLSQL.run
    (
        testPackage_in => 'ut_demo',
        subprogram_in => '%ess'
    );
end;
/

Secondly, it appears to assume the UT_ prefix for any procedure so this…

begin
    utPLSQL.run
    (
        testPackage_in => 'ut_demo',
        subprogram_in => 'ut_success'
    );
end;
/

…results in…

notfound

Now we know how to execute our tests, we can take a look at applying the framework to some application code…

The Footie Application

Before we go any further, here’s a quick re-cap of the Application we’ll be testing.
It’s Sprint 1 and our User Stories are :

  1. Add a competition – tests insert into a table with a Primary Key
  2. Add a Tournament – insert into a table with a Foreign Key constraint
  3. Remove Tournament – delete from a table
  4. Remove Competitions – delete from a Parent table
  5. Edit a Tournament – update of a record in a table
  6. View Tournaments by Competition – select multiple records using an in/out ref cursor
  7. Bulk Upload Competitions – insert records in bulk using LOG ERRORS
  8. Add a Team to a Tournament – insert a record using a synthetic foreign key

the application data model so far looks like this :

sprint1_data_model

There are two packages that we are writing tests against. The signature for MANAGE_COMPETITIONS is :

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 for MANAGE_TOURNAMENTS :

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;
/

The test owning schema

The tests for the FOOTIE application will be owned by a schema called UTP_FOOTIE. I’ve created this user with the following script :

set serveroutput on size unlimited
set verify off
accept passwd prompt 'Enter password for UTP_FOOTIE : ' hide
create user utp_footie identified by &passwd
    default tablespace users
    temporary tablespace temp
/

grant create session, create table, create procedure, create sequence,
    create view to utp_footie
/

alter user utp_footie quota unlimited on users
/

…and granted privileges on the Application’s objects by running…

set serveroutput on size unlimited
spool utp_footie_grants.log

declare
    l_priv varchar2(30);
begin

    for r_object in
    (
        select object_name, object_type
        from dba_objects
        where owner = 'FOOTIE'
        and object_type in ('PACKAGE', 'SEQUENCE', 'TABLE', 'VIEW')
    )
    loop
        l_priv :=
        case r_object.object_type
            when 'PACKAGE' then 'EXECUTE'
            when 'TABLE' then 'ALL'
            else 'SELECT'
        end;
        dbms_output.put_line('Granting '||l_priv||' on '||r_object.object_name);
        execute immediate 'grant '||l_priv||' on footie.'||r_object.object_name||' to UTP_FOOTIE';
    end loop;
end;
/

spool off

…which has the benefit of being able to be re-run without error any time we add database objects to the Application codebase.

When we execute this, we can see that the following object privileges have been granted :

Granting ALL on ERR$_COMPETITIONS
Granting ALL on COMPETITIONS_XT
Granting ALL on COMPETITIONS
Granting EXECUTE on MANAGE_COMPETITIONS
Granting EXECUTE on MANAGE_TOURNAMENTS
Granting SELECT on TOURN_ID_SEQ
Granting ALL on TOURNAMENT_TEAMS
Granting ALL on TOURNAMENTS

PL/SQL procedure successfully completed.

The Test Helper Package

As mentioned previously, I’ll be using a “library” package to hold code to assist test setup, teardown and validation.

This is relevant to your use of the assertion API because, if you can resolve testing results down to a boolean result using a helper function in such a package, you can reduce the range of assertions you need to use for your testing.

For the FOOTIE application, I’ve created a helper package called FOOTIE_UT_HELPER in the UTP_FOOTIE schema. Yes, the name could probably do with some work.
The package header is :

create or replace package footie_ut_helpers
as

	-- Setup routines

	-- Get a COMPETITIONS.COMP_CODE value that does not already exist
	function get_new_comp_code
		return footie.competitions.comp_code%type;

	-- Get the ID for a Tournament
	function get_tourn_id
    (
		i_code footie.tournaments.comp_code%type,
		i_year_end footie.tournaments.year_completed%type,
		i_host footie.tournaments.host_nation%type
	)
		return footie.tournaments.id%type;

	-- Make sure that a COMPETITIONS record exists and create it if it doesn't.
	procedure ensure_comp_exists
	(
		i_code in footie.competitions.comp_code%type,
		i_name in footie.competitions.comp_name%type default 'Yet Another Test',
		i_desc in footie.competitions.description%type default null
	);

	-- Make sure that a TOURNAMENTS record exists and create it if it doesn't.
	procedure ensure_tourn_exists
	(
		i_code footie.tournaments.comp_code%type,
		i_year_end footie.tournaments.year_completed%type,
		i_host footie.tournaments.host_nation%type,
		i_teams footie.tournaments.number_of_teams%type default 4,
		i_year_start footie.tournaments.year_started%type default null
	 );

	-- Validation routines

	-- check that competition exists
	function competition_exists( i_code footie.competitions.comp_code%type)
		return boolean;

	-- check that a tournament exists
	function single_tourn_record_exists
	(
		i_code footie.tournaments.comp_code%type,
		i_year_end footie.tournaments.year_completed%type,
		i_host footie.tournaments.host_nation%type default null
	)
		return boolean;

end footie_ut_helpers;
/

If you’ve read the SQLDeveloper Unit Testing post, then the code in the package body will look familiar…

create or replace package body footie_ut_helpers
as

	function get_new_comp_code
		return footie.competitions.comp_code%type
	is
	--
	-- Generate a competition.comp_code that doesn't already exist.
	-- The code will be the string 'UT' with an integer concatenated
		l_comp_code footie.competitions.comp_code%type;
	begin
        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)
        into l_comp_code
        from suffix
        where numeral is not null;

		return l_comp_code;

	end get_new_comp_code;

	function get_tourn_id
    (
		i_code footie.tournaments.comp_code%type,
		i_year_end footie.tournaments.year_completed%type,
		i_host footie.tournaments.host_nation%type
	)
		return footie.tournaments.id%type
	is
		l_id footie.tournaments.id%type;
	begin
		select id
		into l_id
		from footie.tournaments
		where comp_code = i_code
		and year_completed = i_year_end
		and host_nation = i_host;

		return l_id;
	end get_tourn_id;

	procedure ensure_comp_exists
	(
		i_code in footie.competitions.comp_code%type,
		i_name in footie.competitions.comp_name%type default 'Yet Another Test',
		i_desc in footie.competitions.description%type default null
	)
	is
	--
	-- If competition does not already exist then create it
	--
	begin
		merge into footie.competitions
		using dual
		on ( comp_code = i_code)
		when not matched then
			insert( comp_code, comp_name, description)
			values( i_code, i_name, i_desc);
	end ensure_comp_exists;

	procedure ensure_tourn_exists
	(
		i_code footie.tournaments.comp_code%type,
		i_year_end footie.tournaments.year_completed%type,
		i_host footie.tournaments.host_nation%type,
		i_teams footie.tournaments.number_of_teams%type default 4,
		i_year_start footie.tournaments.year_started%type default null
	 )
	 is
	 --
	 -- Create tournament if it does not already exist
	 --
	 begin
		merge into footie.tournaments
		using dual
		on
		(
			comp_code = i_code
			and year_completed = i_year_end
			and nvl(host_nation, 'X') = nvl(i_host, 'X')
		 )
		 when not matched then
			insert( id, comp_code, year_completed, host_nation, year_started, number_of_teams)
			values( footie.tourn_id_seq.nextval, i_code, i_year_end, i_host, i_year_start, i_teams);
	end ensure_tourn_exists;

	function competition_exists( i_code footie.competitions.comp_code%type)
		return boolean
	is
	--
	-- Check that there is exactly one competition record with a comp_code
	-- of i_code.
	-- If one then return true.
	-- Otherwise (i.e. either none or more than one), then return false.
	--
		l_dummy pls_integer;
	begin
		select null
		into l_dummy
		from footie.competitions
		where comp_code = i_code;

		return true;
	exception when no_data_found or too_many_rows then
			return false;
	end competition_exists;

	function single_tourn_record_exists
	(
		i_code footie.tournaments.comp_code%type,
		i_year_end footie.tournaments.year_completed%type,
		i_host footie.tournaments.host_nation%type default null
	)
	return boolean
	is
		l_dummy pls_integer;
	begin
		select null
		into l_dummy
		from footie.tournaments
		where comp_code = i_code
		and year_completed = i_year_end
		and nvl(host_nation,'X') = nvl(i_host, 'X');
		return true;
	exception when no_data_found or too_many_rows then
		return false;
	end single_tourn_record_exists;
end footie_ut_helpers;
/

Now, finally, we can get on with the tests…

The Assertion API

In order for a test to be executed utPLSQL must call the assertion API.
Whilst most of the test cases you need to write can be handled by one or two API members, it’s worth running through some others that may be utilised in the tests that we’re writing for our User Stories. Let’s start with one that we’ve already met…

utAssert.eq

The first test we’re doing is to make sure that we can add a new competition.
Rather than doing anything complicated like copying and comparing before and after versions of tables, we’re simply going to use the competition_exists function that we’ve specified in our helper package and then use the equality assertion we looked at earlier.
We will be doing a proper table comparison a bit later. For now though…

create or replace package ut_add_competitions
as
	procedure ut_setup;
	procedure ut_add_competition_normal;
	procedure ut_teardown;
end ut_add_competitions;
/

…and the body…

create or replace package body ut_add_competitions
as
	procedure ut_setup is begin null; end ut_setup;

	procedure ut_add_competition_normal
	is
		l_code footie.competitions.comp_code%type;
	begin
		-- Setup
		l_code := footie_ut_helpers.get_new_comp_code;
		-- Execute
		footie.manage_competitions.add_competition( l_code, 'Test', null);
		-- Validate
		utAssert.eq
		(
			'Add a competition',
			footie_ut_helpers.competition_exists(l_code),
			true
		);
	end ut_add_competition_normal;

	procedure ut_teardown
	is
	begin
		rollback;
	end ut_teardown;
end ut_add_competitions;
/

As well as the assertion, notice that we’re doing the setup in the actual test procedure itself. This is because the second test we do for this procedure requires different setup steps.
For the moment, we’re leaving the teardown to the framework as we only have a single test.

Anyway, running this works as expected.

add_comp1

One advantage of using the equality assertion in this way is that the test code invokes the target procedure directly rather than through a Dynamic SQL statement as happens in some other assertion API members. As a result, the dependency between the test package and the target package is recorded in the data dictionary…

select name as test_package
from all_dependencies
where referenced_owner = 'FOOTIE'
and referenced_name = 'MANAGE_COMPETITIONS'
and referenced_type = 'PACKAGE'
/

TEST_PACKAGE
------------------------------
UT_ADD_COMPETITIONS

This sort of thing comes in quite handy if, like me, you decide to go down the route of not having a one-to-one mapping between your test packages and the packages that they are testing as it allows you to dynamically build a suite to run all of the tests for a given package.
As we’ll see shortly, there are alternatives to this approach should you find that you absolutely have to use an assertion that executes the target procedure dynamically. Speaking of which…

utAssert.throws – testing for an Exception

Our second test is to ensure that the application will not permit us to create a duplicate Competition record.
So, our test needs to check that the call to the target procedure failed, and that it failed with the expected error.

So, we add the following procedure to our package ( amending the package header as well, of course) …

...
	procedure ut_add_competition_duplicate
	is
	-- Test adding a duplicate competition
		l_code footie.competitions.comp_code%type := 'HIC';
		l_name footie.competitions.comp_name%type := 'British Home Championship';
		l_stmnt varchar2(4000);
	begin
		-- Setup
		footie_ut_helpers.ensure_comp_exists( i_code => l_code, i_name => l_name);
		-- Build a statement to execute dynamically..
		l_stmnt := 'footie.manage_competitions.add_competition('||chr(39)||l_code||chr(39)||', '||chr(39)||l_name||chr(39)||');';
		-- Execute and Validate - expected error is ORA-0001
		utAssert.throws
		(
			'Cannot add a duplicate Competition Code',
			l_stmnt,
			-1
		);
	end ut_add_competition_duplicate;
...

Remember, now we have two tests in our package, we need to specify which we want to run, if we don’t want to run them all…

set serveroutput on size unlimited
begin
    utPLSQL.run( testPackage_in => 'ut_add_competitions', subProgram_in => 'add_competition_duplicate');
end;
/

throws_pass

Whilst this does the trick, it’s not exactly elegant. The limitations with this particular assertion are explored here in some detail by Jacek.
Fortunately, because we’re using utPLSQL we do have the full power and flexibility of PL/SQL at our disposal.
We know that the error code for the last statement executed will be held in the SQLCODE environment variable.
Therefore, as an alternative, we could do this…

...
	procedure ut_add_competition_duplicate
	is
	-- Test adding a duplicate competition
		l_code footie.competitions.comp_code%type := 'HIC';
		l_name footie.competitions.comp_name%type := 'British Home Championship';
		l_err_code number := 0;
	begin
		-- Setup
		footie_ut_helpers.ensure_comp_exists( i_code => l_code, i_name => l_name);
		-- Execute - use a nested block to handle the expected exception
		begin
			footie.manage_competitions.add_competition(l_code, l_name);
		exception when others then
			l_err_code := sqlcode;
		end;
		-- Validate - this time using eq assertion expected error is ORA-0001
		utAssert.eq( 'Cannot add a duplicate Competition Code', l_err_code, -1 );
	end ut_add_competition_duplicate;
...

…which will also do the job…

eq_exception

The final Add Competitions Test Package

As mentioned previously, there may be occasions when you need a way of explicitly associating a test with a target package at runtime.
The most obvious example is when you want to dynamically build a test suite to run all the available tests on a given package.
Even if we use an assertion which calls the target package dynamically, we can still do this via the data dictionary as we can just add a comment with a standard format into the test package header.
We can then search for the comment in ALL_SOURCE.

For example, if we amend the UT_ADD_COMPETITION package header like this :

create or replace package ut_add_competitions
as
-- utplsql_target_object = MANAGE_COMPETITIONS

	procedure ut_setup;
	procedure ut_teardown;
	procedure ut_add_competition_normal;
	procedure ut_add_competition_duplicate;
end ut_add_competitions;
/

We can then identify it as being a test for MANAGE_COMPETITIONS by using this query :

select name
from user_source
where type = 'PACKAGE'
and text like '%utplsql_target_object%'
and substr(text, instr(text, '=',1,1) + 2, length('MANAGE_COMPETITIONS') ) = 'MANAGE_COMPETITIONS'
/

Yes, trawling through USER_SOURCE is not exactly elegant. However, using a package variable would also be problematic, as I’ll demonstrate later.
As for a function to simply return the name of the target package, this wouldn’t work because the function would need to be public and execution of the test would result in a complaint from the framework along the lines of …

...
FAILURE - UT_LOAD_COMPETITIONS.UTPLSQL_TARGET_OBJECT: Unable to run "UTP_FOOTIE".UT_LOAD_COMPETITIONS.UTPLSQL_TARGET_OBJECT: ORA-06550: line 1, column 8:
PLS-00221: 'UTPLSQL_TARGET_OBJECT' is not a procedure or is undefined
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
...

There’s one final tweak to apply to the Test Package Body. Here, we’ll make sure that the teardown routine is run after each test simply by explicitly calling the ut_teardown procedure at the end of each test procedure :

create or replace package body ut_add_competitions
as
	procedure ut_setup is begin null; end ut_setup;

	procedure ut_teardown
	is
	begin
		rollback;
	end ut_teardown;

	procedure ut_add_competition_normal
	is
	-- Add a new Competition
		l_code footie.competitions.comp_code%type;
	begin
		-- Setup
		l_code := footie_ut_helpers.get_new_comp_code;
		-- Execute
		footie.manage_competitions.add_competition( l_code, 'Test', null);
		-- Validate
		utAssert.eq
		(
			'Add a competition',
			footie_ut_helpers.competition_exists(l_code),
			true
		);
		ut_teardown;
	end ut_add_competition_normal;

	procedure ut_add_competition_duplicate
	is
	-- Test adding a duplicate competition
		l_code footie.competitions.comp_code%type := 'HIC';
		l_name footie.competitions.comp_name%type := 'British Home Championship';
		l_err_code number := 0;
	begin
		-- Setup
		footie_ut_helpers.ensure_comp_exists( i_code => l_code, i_name => l_name);
		-- Execute - use a nested block to handle the expected exception
		begin
			footie.manage_competitions.add_competition(l_code, l_name);
		exception when others then
			l_err_code := sqlcode;
		end;
		-- Validate - this time using eq assertion expected error is ORA-0001
		utAssert.eq( 'Cannot add a duplicate Competition Code', l_err_code, -1 );
		ut_teardown;
	end ut_add_competition_duplicate;
end ut_add_competitions;
/

Next up…

utAssert.this

This is probably the simplest assertion available. It accepts a message and a statement returning a boolean…and returns it.
It’s also rather versatile, especially when you use “helper” functions. For example, my test for adding a tournament to a Competition is…

...
	procedure ut_add_tournament_normal
	is
	begin
		-- Setup
		footie_ut_helpers.ensure_comp_exists(g_code);
		-- Execute
		footie.manage_tournaments.add_tournament( i_code => g_code, i_year_end => g_year_end, i_teams => g_teams, i_host => g_host);
		-- Validate
		utAssert.this
		(
			'Tournament added for Competition',
			footie_ut_helpers.single_tourn_record_exists(g_code, g_year_end, g_host)
		);
		-- Teardown
		rollback;
	end ut_add_tournament_normal;
...

… and for checking that a user-defined exception is raised when I try to add a tournament with an end date prior to the start date…

...
	procedure ut_add_tourn_start_after_end
	is
		l_err_code number := 0;
		l_year_start footie.tournaments.year_started%type := 1918;
	begin
		footie_ut_helpers.ensure_comp_exists(g_code);
		begin
			footie.manage_tournaments.add_tournament
			(
				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;
		end;
		utAssert.this('Cannot add a tournament that ends before it starts', l_err_code = -20000);
		rollback;
	end ut_add_tourn_start_after_end;
...

In fact, all five tests required by the Acceptance Criteria for this story can be written using the “this” assertion.

The package header looks like this :

create or replace package ut_add_tournament
as
-- utplsql_target_object = MANAGE_TOURNAMENTS
	procedure ut_setup;
	procedure ut_teardown;
	procedure ut_add_tournament_normal;
	procedure ut_add_tournament_invalid_comp;
	procedure ut_add_tournament_null_comp;
	procedure ut_add_tournament_duplicate;
	procedure ut_add_tourn_start_after_end;
end ut_add_tournament;
/

…and the body…

create or replace package body ut_add_tournament
as
	--
	-- Global package variables to hold default parameter values for
	-- each mandatory IN parameter to MANAGE_TOURNAMENTS.ADD_TOURNAMENT
	-- In this case, values from the inaugural Copa America
	--
	g_code footie.tournaments.comp_code%type := 'CA';
	g_year_end footie.tournaments.year_completed%type := 1916;
	g_teams footie.tournaments.number_of_teams%type := 4;
	g_host footie.tournaments.host_nation%type := 'ARGENTINA';
	--
	-- The tests in this package each require different setup routines
	-- so the setup and teardown code is in the test procedures themselves.
	-- the UT_SETUP and UT_TEARDOWN procedures are therefore included solely
	-- to keep utPLSQL happy
	--
	procedure ut_setup is begin null; end ut_setup;
	procedure ut_teardown is begin null; end ut_teardown;

	procedure ut_add_tournament_normal
	is
	begin
		-- Setup
		footie_ut_helpers.ensure_comp_exists(g_code);
		-- Execute
		footie.manage_tournaments.add_tournament( i_code => g_code, i_year_end => g_year_end, i_teams => g_teams, i_host => g_host);
		-- Validate
		utAssert.this
		(
			'Tournament added for Competition',
			footie_ut_helpers.single_tourn_record_exists(g_code, g_year_end, g_host)
		);
		-- Teardown
		rollback;
	end ut_add_tournament_normal;

	procedure ut_add_tournament_invalid_comp
	is
		l_code footie.tournaments.comp_code%type;
		l_err_code number := 0;
	begin
		-- Setup
		l_code := footie_ut_helpers.get_new_comp_code;
		-- Execute - nested block as expecting exception...
		begin
			footie.manage_tournaments.add_tournament( i_code => l_code, i_year_end => g_year_end, i_teams => g_teams);
		exception when others then
			l_err_code := sqlcode;
		end;
		-- Validate
		utAssert.this('Cannot add tournament for invalid Competition', l_err_code = -2291);
		-- Teardown
		rollback;
	end ut_add_tournament_invalid_comp;

	procedure ut_add_tournament_null_comp
	is
		l_err_code number := 0;
	begin
		-- No setup required.
		begin
			footie.manage_tournaments.add_tournament( i_code => null, i_year_end => g_year_end, i_teams => g_teams);
		exception when others then
			l_err_code := sqlcode;
		end;
		utAssert.this('Cannot add tournament for unspecified competition', l_err_code = -1400);
		rollback;
	end ut_add_tournament_null_comp;

	procedure ut_add_tournament_duplicate
	is
		l_err_code number := 0;
	begin
		footie_ut_helpers.ensure_comp_exists( g_code);
		footie_ut_helpers.ensure_tourn_exists( g_code, g_year_end, g_host);
		begin
			footie.manage_tournaments.add_tournament( i_code => g_code, i_year_end => g_year_end, i_teams => g_teams, i_host => g_host);
			exception when others then
				l_err_code := sqlcode;
		end;
		utAssert.this('Cannot add the same tournament twice', l_err_code = -1);
		rollback;
	end ut_add_tournament_duplicate;

	procedure ut_add_tourn_start_after_end
	is
		l_err_code number := 0;
		l_year_start footie.tournaments.year_started%type := 1918;
	begin
		footie_ut_helpers.ensure_comp_exists(g_code);
		begin
			footie.manage_tournaments.add_tournament
			(
				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;
		end;
		utAssert.this('Cannot add a tournament that ends before it starts', l_err_code = -20000);
		rollback;
	end ut_add_tourn_start_after_end;
end ut_add_tournament;
/		 

The result is :

this_success

If you like your assertions a bit more in-line – i.e. without the aid of a helper function…

utAssert.eqQueryValue

As well as the obligatory message, this assertion accepts a query in the form of a VARCHAR2, which it then executes and checks against a value that’s also passed in. The value can be a VARCHAR2, a NUMBER, or a DATE.

To demonstrate this assertion type, we’re testing updates to tournament records.
The test package header looks like this :

create or replace package ut_edit_tournament
as
-- utplsql_target_object = MANAGE_TOURNAMENTS
	function tournament_count
	(
		i_id footie.tournaments.id%type,
		i_teams footie.tournaments.number_of_teams%type,
		i_year_start footie.tournaments.year_started%type default null
	)
		return number;

	procedure ut_setup;
	procedure ut_update_no_of_teams;
	procedure ut_update_year_started;
	procedure ut_teardown;
end;
/

For these particular tests, I can allow utPLSQL to run the setup and teardown in the default manner i.e. do the setup, run both the tests, do the teardown.
The first test uses the assertion in what you may regard as the “default” manner …

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

In this case, the test will pass if the result of the query is 1.

The next test makes use of the fact that utPLSQL will ignore any private package members and simply calls a function in the same pacakge.

This makes the call a bit neater…

...
	procedure ut_update_year_started
	is
	begin
		-- Execute
		footie.manage_tournaments.edit_tournament( i_id => g_id, i_teams => null, i_year_start => 2015);
		-- Validate
		utAssert.eqQueryValue
		(
			'Confirm Year Started Updated',
			'select ut_edit_tournament.tournament_count('||g_id||', 16, 2015) from dual',
			1
		);
	end ut_update_year_started;
...

Note that utPLSQL will refuse to recognize the function that’s in the package it’s executing unless you qualify it with the package name.

Using this assertion, you do get a fair amount of information when you execute the tests…

eqqueryval_success

Having said that, you may consider, in this instance at least, that you’re better off with some helper functions and the good old utAssert.eq assertion.
By contrast…

utAssert.eq_refc_query

…is one of those assertions that has the potential to save much wailing and gnashing of teeth as it deals specifically with our old friend, the Ref Cursor.
It is a continuing conundurum as to why a construct which makes interaction with PL/SQL so much easier from another language, remains such a pain to deal with within PL/SQL itself.
The utPLSQL framework deals with it as well as can be expected…

In our application, it’s the MANAGE_TOURNAMENTS.LIST_TOURNAMENTS procedure that takes an In/Out SYS_REFCURSOR as an argument.
Remember, the signature of this procedure is :

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

To call it from utPLSQL, we need to

  1. declare an array for the parameter values
  2. assign parameters and values to the array using the UTPLSQL_UT_REG* procedures (using the OUT version for IN/OUT parameters)
  3. Pass the parameters into the assertion API together with a Query for it to check against

All of which looks something like this :

...
	procedure ut_list_by_competition
	is
		l_params utPLSQL_util.utplsql_params;
		l_stmnt varchar2(4000);
	begin
		l_stmnt := 'select * from footie.tournaments where comp_code = '||chr(39)||g_code||chr(39);
		utplsql_util.reg_in_param(par_pos => 1, par_val => 'WC', params => l_params);
		-- Use the REG_OUT_PARAM procedure, even though the refcursor is actually an in/out parameter...
		utplsql_util.reg_out_param(par_pos => 2, par_type => 'REFCURSOR', params =>    l_params);
		utAssert.eq_refc_query
		(
			p_msg_nm => 'Refcursor check',
			proc_name => 'footie.manage_tournaments.list_tournaments',
			params => l_params,
			cursor_position => 2,
			qry => l_stmnt
		);
	end ut_list_by_competition;
...

The complete test package looks like this. First the header :

create or replace package ut_list_tournaments
as
-- utplsql_target_object = MANAGE_TOURNAMENTS
	procedure ut_setup;
	procedure ut_list_by_competition;
	procedure ut_teardown;
end;
/

…and the body

create or replace package body ut_list_tournaments
is

	g_code footie.competitions.comp_code%type := 'WC';

	procedure add_tournament
	(
		i_year footie.tournaments.year_completed%type,
		i_host footie.tournaments.host_nation%type,
		i_teams footie.tournaments.number_of_teams%type
	)
	is
	--
	-- Private package procedure to add tournaments
	--
	begin
		merge into footie.tournaments
		using dual
		on
		(
			comp_code = g_code
			and year_completed = i_year
			and host_nation = i_host
		)
		when not matched then
			insert ( id, comp_code, year_completed,
				host_nation, year_started, number_of_teams)
			values( footie.tourn_id_seq.nextval, g_code, i_year,
				i_host, null, i_teams);
	end add_tournament;        

	procedure ut_setup is
	begin
		merge into footie.competitions
		using dual
			on ( comp_code = g_code)
			when not matched then
			insert( comp_code, comp_name, description)
			values(g_code, 'World Cup', 'FIFA World Cup');

		add_tournament(1930, 'URUGUAY', 13);
		add_tournament(1934, 'ITALY', 16);
		add_tournament(1938, 'FRANCE', 16);
		add_tournament(1950, 'BRAZIL', 13);

	end ut_setup;

	procedure ut_list_by_competition
	is
		l_params utPLSQL_util.utplsql_params;
		l_stmnt varchar2(4000);
	begin
		l_stmnt := 'select * from footie.tournaments where comp_code = '||chr(39)||g_code||chr(39);
		utplsql_util.reg_in_param(par_pos => 1, par_val => 'WC', params => l_params);
		-- Use the REG_OUT_PARAM procedure, even though the refcursor is actually an in/out parameter...
		utplsql_util.reg_out_param(par_pos => 2, par_type => 'REFCURSOR', params =>    l_params);
		utAssert.eq_refc_query
		(
			p_msg_nm => 'Refcursor check',
			proc_name => 'footie.manage_tournaments.list_tournaments',
			params => l_params,
			cursor_position => 2,
			qry => l_stmnt
		);
	end ut_list_by_competition;

	procedure ut_teardown is
	begin
		rollback;
	end ut_teardown;
end ut_list_tournaments;
/

Executing the test should result in something like…

ref_cur_test

Finally, for those times where you just have to compare tables…

utAssert.eqTable and eqTabCount

With DML changes affecting just a few rows, it’s generally practical for a Rollback of the current transaction to serve as a Teardown process for a test. This obviates the need for us to create a backup of the table as part of the test Setup.
There are times however, when something a bit more substantial is required.
Our last User Story covers the bulk upload of Competitions to the application. As is common with an upload job of this type, the PL/SQL uses LOG ERRORS to handle any records that don’t get loaded due to an error. As this implicitly runs an autonomous transaction to save exceptions into the error table (ERR$_COMPETITIONS in this case), we need to create a backup of the table before we run the test code. We then need to use this backup to re-set the table to it’s original state as part of the test Teardown.
All of which can be achieved using the following in utPLSQL.
NOTE that the WHEN OTHERS THEN NULL exception handlers are lifted right out of the examples section in the documentation .
Therefore, any irate correspondence about this particular construct should be addressed to :
Mr S. Feuerstein
Oracle Towers,
Database Land

Alternatively, you could simply accept that there is an exception to every rule…

create or replace package ut_load_competitions
as
-- utplsql_target_object = MANAGE_COMPETITIONS
	procedure ut_setup;
	procedure ut_check_upload;
	procedure ut_teardown;
end;
/
create or replace package body ut_load_competitions
as
	procedure add_comp
	(
		i_code footie.competitions.comp_code%type,
		i_name footie.competitions.comp_name%type,
		i_desc footie.competitions.description%type
	)
	is
	--
	-- Add competition records to the expected results tables
	--
		l_count pls_integer := 0;
		l_stmnt varchar2(4000);
	begin
		-- If this record already exists, it should end up in the
		-- error table as a duplicate
		select count(*)
		into l_count
		from footie.competitions
		where comp_code = i_code;

		-- Note that the insert statements need to be dynamic as the target tables
		-- do not exist at compile time.
		if l_count = 0 then
			l_stmnt := 'insert into competitions_expctd( comp_code, comp_name, description) values( :1, :2, :3)';
		else
			l_stmnt := 'insert into err$competitions_expctd( ora_err_number$, ora_err_mesg$, ora_err_rowid$, '
				||'ora_err_optyp$, ora_err_tag$, comp_code, comp_name, description) '
				||q'[values(1, 'ORA-00001: unique constraint (FOOTIE.COMP_PK) violated', null, 'I', null, :1, :2, :3)]';
		end if;
		execute immediate l_stmnt using i_code, i_name, i_desc;
	end add_comp;

	procedure ut_setup
	is
		l_stmnt varchar2(4000);
	begin
		-- Ensure we have a clean slate...
		ut_teardown;
		-- Don't need a backup for the COMPETITIONS table as rollback will undo any changes we've made as part of this test,
		-- but we do for ERR$_COMPETITIONS...
		execute immediate 'create table err$competitons_bu as select rowid as bu_rowid, err.* from footie.err$_competitions err';

		-- Create and populate expected results tables...
		execute immediate 'create table competitions_expctd as select * from footie.competitions';
		execute immediate 'create table err$competitions_expctd as select * from footie.err$_competitions';

		-- populate the expected results tables
		-- NOTE - these records match the contents of competitions.csv
		add_comp('HIC', 'Home International Championship', 'British Home International Championship');
		add_comp( 'CA','Copa America', 'Copa America (South American Championship until 1975)');
		add_comp( 'OLY', 'Olympic Football Tournament', 'The Olympics');
		add_comp('WC','World Cup','The FIFA World Cup');
		add_comp( 'CEIC','Central European International Cup','Central European International Cup - a forerunner to the European Championships');
		add_comp( 'EURO', 'European Championship', 'UEFA European Championship');

		-- The final record in the file is a deliberate duplicate to ensure at least one error record...
		l_stmnt := 'insert into err$competitions_expctd( ora_err_number$, ora_err_mesg$, ora_err_rowid$, '
			||'ora_err_optyp$, ora_err_tag$, comp_code, comp_name, description) '
			||q'[values(1, 'ORA-00001: unique constraint (FOOTIE.COMP_PK) violated', null, 'I', null,]'
			||q'[ 'HIC', 'Home International Championship', 'British Home International Championship')]';
		execute immediate l_stmnt;
	end ut_setup;

	procedure ut_check_upload
	is
		-- Despite all of the DDL flying around in the setup and teardown processes,
		-- this proc needs to be done in a single transaction (for the competitions table at least)
		-- so that I can use rollback to teardown the test changes for that table.
		-- ERR$_COMPETITION is a different story...
	begin
		-- Execute
		footie.manage_competitions.upload_competitions;
		-- Validate that there is an exact match between the records we've loaded and what we expected...
		utAssert.eqTable('Check records uploaded successfully', 'FOOTIE.COMPETITIONS', 'COMPETITIONS_EXPCTD');
		-- ... and that we have the expected number of error records...
		utAssert.eqTabCount('Check error record count', 'FOOTIE.ERR$_COMPETITIONS', 'ERR$COMPETITIONS_EXPCTD');
	end ut_check_upload;

	procedure ut_teardown
	is
	begin
		rollback;
		begin
			-- Needs to be dynamic because the temp table
			-- doesn't exist at compile time...
			execute immediate '
			delete from footie.err$_competitions
			where rowid not in (select bu_rowid from err$competitons_bu)';
		exception when others then null;
		end;

		begin
			execute immediate 'drop table err$competitons_bu';
		exception when others then 	null;
		end;

		begin
			execute immediate 'drop table err$competitions_expctd';
		exception when others then null;
		end;

		begin
			execute immediate 'drop table competitions_expctd';
		exception when others then null;
		end;
	end ut_teardown;
end ut_load_competitions;
/

The comments should help to clarify what’s happening here.
Essentially, we’re creating copies of both the COMPETITIONS and ERR$_COMPETITIONS table and populating them with the expected records.
To compare the contents of the COMPETITIONS table with that expected, we simply pass both the application table, and our expected results table, to the eqTable assertion.
With the error table, we’re just checking that we have the expected number of records. Once again, the eqTabCount takes the names of the two tables to compare, along with the standard test message text.

Note that the file being read by the external table – competitions.csv 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

Yes, I should probably create this on the fly as part of the test setup but I’ve kept things (fairly) simple in order to focus on the assertions.

To step through how this all works, let’s take a look at our tables before the test executes. COMPETITIONS looks like this :

select comp_code, comp_name, description
from footie.competitions
/

COMP_ COMP_NAME                                          DESCRIPTION
----- -------------------------------------------------- --------------------
WC    World Cup                                          FIFA World Cup      

1 row selected. 

and the ERR$_COMPETITIONS table…

select count(*)
from footie.err$_competitions
/

  COUNT(*)
----------
         0

Next, we run our test which should insert up to six records in the COMPETITIONS table and at least one in ERR$_COMPETITIONS…

eqtab_run

Once the test has run, we re-execute these queries and confirm that the COMPETITIONS and ERR$_COMPETITIONS tables have returned to their original state.

We can also confirm that the backup and expected result tables we created for the duration of the test have been dropped :

select table_name
from user_tables
where table_name in ('ERR$COMPETITONS_BU', 'ERR$COMPETITIONS_EXPCTD', 'COMPETITIONS_EXPCTD')
/
no rows selected

That just about covers the Assertion API ( or at least, that bits of it that have been of interest in this sprint), but what about making sure that you can group and run all of the tests for a specific package in one go ?
Glad you asked….

Suites

As with most Test Frameworks, utPLSQL provides the ability to group tests together into Test Suites.
It encourages the building and execution of such suites dynamically to help ensure that it’s always the very latest test code that gets executed.
As mentioned previously, my preferred approach is to use a comment to identify the target package for a test package.
There are, ostensibly, less inelegant ways of doing this, but they come with their own problems.

Specifying tests at design time

To start with, let’s take a look at the simplest means of doing this, i.e. by hard-coding the tests we want to include in the suite…

set serveroutput on size unlimited
begin
	-- try to persuade utPLSQL not to try recompiling...
	utConfig.autocompile(false);
	-- create a suite...
	utSuite.add('MANAGE_COMPETITIONS');
	-- add test packages to the suite
	utPackage.add( suite_in => 'MANAGE_COMPETITIONS', package_in => 'UT_ADD_COMPETITIONS');
	utPackage.add( suite_in => 'MANAGE_COMPETITIONS', package_in => 'UT_LOAD_COMPETITIONS');
	-- run suite - NOTE - using runSuite rather than testSuite means that you can use
	-- tests where there is no correlation between test name and target package name
	utPLSQL.runSuite('MANAGE_COMPETITIONS');
end;
/

When we execute the code we get…

suite_success

The Suite Smell Of Success…so much that it won’t fit into a single screenshot

The overhead is that we’d need to amend this script every time we added or removed a test for this package. As an alternative…

The Package Variable Approach

You may consider using a Package Variable to identify the Target Package. For example :

create or replace package ut_add_competitions
as

	utplsql_target_object constant varchar2 := 'MANAGE_COMPETITIONS;

	procedure ut_setup;
	procedure ut_add_competition_normal;
	procedure ut_add_competition_duplicate;
	procedure ut_teardown;
end ut_add_competitions;
/

To get this value back at runtime, you’d need something like this ( edited for the sake of brevity) …

declare
	lc_package_name constant varchar2(30) := 'MANAGE_COMPETITIONS';
	l_var_name varchar2(100);
	l_target_package varchar2(30);
begin
...
	for r_test in
	(
		select object_name
		from user_objects
		where object_type = 'PACKAGE'
		and object_name like 'UT\_%' escape '\'
	)
	loop
		l_var_name := r_test.object_name||'.utplsql_target_object';
		execute immediate 'begin 😡 := '||l_var_name||'; end;' using out l_target_package;
		if l_target_package = lc_package_name then
			utPackage.add(suite_in => lc_suite_name, package_in => r_test.object_name);
		end if;
	end loop;
...

The problem with this approach is that, if you do have a package with a name that starts ‘UT_’ but which does not contain a variable called utplsl_target_object then you’ll get a compile time error for the Native Dynamic SQL (NDS) statement …

ORA-06550: line 1, column 34:
PLS-00302: component 'UTPLSQL_TARGET_OBJECT' must be declared

As it’s a compile time error, you won’t be able to handle it with an exception block within the dynamic SQL itself.
In order to avoid this, you’ll need some code to check that the test package you’ve retrieved from USER_OBJECTS actually includes a variable with this name.
The only way to do that ( up to 11g at least) is to trawl through USER_SOURCE. OK, that’s not quite true, you can use USER_IDENTIFIERS as well if you have the appropriate PL/SQL compilation settings enabled, but it still appears to be no better than…

Getting the Target Package from Package Header Comments

As a balance between the hard-coded and the dynamic approaches, this looks to be the least worst option…

set serveroutput on size unlimited
declare
	lc_target_package_name constant varchar2(30) := 'MANAGE_COMPETITIONS';
begin
	-- Oi, utPLSQL, stop compiling !
	utConfig.autocompile(false);

	-- create a suite with the same name as the package we're testing...
	utSuite.add(lc_target_package_name);

	-- find the tests for this package...
	for r_test in
	(
		select name
		from user_source
		where type = 'PACKAGE'
		and name like 'UT\_%' escape '\'
		and text like '%utplsql_target_object%'
		and substr(text, instr(text, '=',1,1) + 2, length(lc_target_package_name) ) = lc_target_package_name
	)
	loop
		-- ...and add them to the suite
		utPackage.add( suite_in => lc_target_package_name, package_in => r_test.name);
	end loop;

	-- run the suite
	utPLSQL.runSuite(lc_target_package_name);
end;
/

Ultimately, you may well conclude that the overhead of maintaining test suites is worth the effort when compared to the possibly precarious methods available to you when it comes to generating them on-the-fly.
For now however, I will persist with this dynamic approach. Let’s face it, I’ve already written the test code by now and I don’t want to have to mess about changing it all again. Come to that, I’ve also written and tested the stuff for the final part of our tour through utPLSQL…

Retrieving utPLSQL test execution results programmatically

A quick recap on the history of utPLSQL – it was originally released in 2000. To put this in context, Cruise Control, the first Continuous Integration Framework didn’t emerge into the world until 2001.
It is perhaps understandable, therefore, that utPLSQL doesn’t focus too much on the ability to examine test results programatically, in the way that CI software might need to in determining whether a build is RED (i.e. tests failing) or GREEN ( all OK).

That’s not to say it’s impossible to discover test execution results programatically in utPLSQL.
To demonstrate, the following script…

  1. Arranges all of the tests into suites by target package name
  2. Executes all of the Test Suites
  3. For each test, extracts the package tested, the test run, and the outcome of the test
  4. If any of the tests has failed, it reports that the build is RED (broken). Otherwise that the build is GREEN

…all of which looks something like this…

set serveroutput off
declare
	--
	-- Script to build and execute all relevant Test Suites
	-- and then report test status and report build as GREEEN (if all tests pass) or
	-- RED (if anything failed).
	--
	cursor c_tests is
		select name as test_name,
			rtrim( substr(text, 28)) as target_name
		from user_source
		where type = 'PACKAGE'
		and name like 'UT\_%' escape '\'
		and text like '-- utplsql_target_object = %';	

	type typ_tests is table of c_tests%rowtype index by pls_integer;
	tbl_tests typ_tests; 

begin
	-- Reset the results...
	utResult.init;

	-- Initialization - remove all of the existing suites...
	for r_suite in
	(
		select name
		from ut_suite
	)
	loop
		utSuite.rem( name_in => r_suite.name);
	end loop;

	-- Now add back the test suites
	open c_tests;
	fetch c_tests bulk collect into tbl_tests;
	close c_tests;

	for i in 1..tbl_tests.count loop
		-- create the test package suite if it doesn't already exist.
		begin
			utSuite.add( name_in => tbl_tests(i).target_name, rem_if_exists_in => false);
		exception when dup_val_on_index then null;
		end;
		-- add the test to the relevant suite...
		utPackage.add( tbl_tests(i).target_name, tbl_tests(i).test_name);
	end loop;
	-- Now run the tests...
	for r_suite in
	(
		select name
		from ut_suite
	)
	loop
		utPLSQL.runSuite(r_suite.name);
	end loop;
end;
/

set serveroutput on size unlimited
set lines 130
declare
	l_current_suite varchar2(30) := 'NONE';
	l_build_status varchar2(10) := 'GREEN';
begin
	-- List out the status...
	for r_results in
	(
		with results as
		(
			select sut.name as suite_name,
				pack.name as test_package_name,
			instr(otc.description, '.',1,1) + 1 as proc_start,
			instr(otc.description, ':',1,1) as proc_end,
			otc.description,
			otc.status,
			otc.run_id, otc.tc_run_id
			from utr_outcome otc
			inner join ut_package pack
				on otc.run_id = pack.last_run_id
			inner join ut_suite sut
				on sut.id = pack.suite_id
		)
		select suite_name, test_package_name,
			substr(description, proc_start, proc_end - proc_start) as test_procedure,
			status
		from results
		order by run_id, tc_run_id
	)
	loop
		if r_results.status = 'FAILURE' then
			l_build_status := 'RED';
		end if;
		if r_results.suite_name != l_current_suite then
			l_current_suite := r_results.suite_name;
			dbms_output.new_line;
			dbms_output.put_line(l_current_suite);
		end if;
		dbms_output.put_line(r_results.test_package_name||'.'||r_results.test_procedure||' : '||r_results.status);
	end loop;
	-- Finally, confirm the status of the build
	dbms_output.new_line;
	dbms_output.put_line('Build Status is '||l_build_status);
end;
/

As you can see, the results of the test executions are held in the UTR_OUTCOME table. By joining with UT_PACKAGE we can get the name of the Test package.
Similarly, joining to UT_SUITE gives us the name of the test suite.
Getting the name of the test procedure is a bit more involved as we need to read it as a sub-string of UTR_OUTCOME.DESCRIPTION.

Anyhow, when we execute this we get :

ci_results

Conclusions and Further Reading

Whilst utPLSQL seems to encourage certain practices in terms of writing tests, it remains flexible enough to be useful, whichever approach you decide to take.
It offers a wide variety of assertions which you can utilise. Equally, the basic assertions can be used in conjunction with application specific “helper” functions to provide comprehensive test coverage.
In addition to the extensive documentation availalbe for the tool itself there are a couple of links which may be of interest.

Steven Fuerstein’s article, Getting Started with Testing includes an introduction into utPLSQL.
Oracle Unit Testing with utPLSQL by Brendan Furey offers some real-world insights into using the framework.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s