(Almost) Everything you ever wanted to know about SQLDeveloper Unit Testing but were afraid to ask

The Political fallout from Brexit continues unabated.
In the immediate aftermath of the vote, David Cameron resigned as Prime Minister, triggering a power-struggle to succeed him.
Secret pacts, double-crosses and political “assassination” followed in short order.
It was like watching an episode of Game of Thrones although, mercifully, without the full-frontal nudity.
As for the Labour Party, they have captured the Zeitgeist…by descending into the sort of internal conflict for which the word “internecine” was invented.

Following the current trend that promises are made for breaking, this post has arrived a little bit later than advertised.
I write most of this stuff on the train to and from work, and, as they have been unusually punctual of late, my Sprint 1 velocity is somewhat lower than anticipated.
So, with apologies for my tardiness…

A Quick Overview

When it comes to Unit Testing Tools/Frameworks for PL/SQL, SQLDeveloper Unit Testing can probably be considered the “official” Oracle offering.
After all, SQLDeveloper is shipped with the Oracle Database.

Therefore, this seems like a fitting place to continue my PL/SQL Unit Testing odyssey.

Useful Links and Resources

There is a very good tutorial covering setting up Tests on a Stored Procedure right there in the SQLDeveloper Help, as well as other useful info about SQlDeveloper Unit Testing :


This post from Jeff Smith is a useful introduction to SQLDeveloper Unit Testing, in which he takes a different approach to some of the tasks that I’ve covered here.

For a completely different take on things, this article by Wouter Groeneveld is so good that I will be plagiarising bits of it later on.

Before I go any further, SQLDeveloper Unit Testing is a bit of a mouthful. Additionally I’m never sure if I should be calling it a Tool or a Framework. Therefore, I’m going to refer to it as SUT for the remainder of this post.

Testing Approach

The approach I’ve taken to evaluating SUT (and the other frameworks that I will look at in later posts) is to use the application I introduced last time and to see how I can test some of the User Stories I have for it.
These Stories cover functionality that you would commonly find in a PL/SQL application ( DML operations, ETL data load and Ref Cursor population).
Additionally, I’ve taken the view that the Application Code should not be changed in any way to accommodate any vagaries of SUT.

As it’s the Testing Tool that’s my primary concern here, the Application Code I’ve included here is pretty much the minimum required to get the tests to pass.
It’s not what you’d call Production ready.

Also, as this is my first foray into SUT so some of the Tests may not have been written in the optimum fashion.
The examples below are more a case of me saying “this is how I managed to test this feature in SUT” rather than “this is the recommended way to test this feature”.

Test Environment

These tests have been put together and executed in the current Production version of SQLDeveloper ( 4.1.3).
The database used is Oracle 11g Express Edition.

Toolbars and Buttons

The SUT toolbar looks like this :


From left-to-right, the buttons are :

  • Freeze View
  • Refresh
  • Debug – provides feedback on the runnable bits of your test without actually running it
  • Run

Additionally, I’ll be mentioning the “Plus” button quite a bit. That would be this :


Right, I think we’re just about ready to go.
Before we start writing any tests, we need to do a bit of groundwork…

SQLDeveloper Unit Testing – Configuration

First things first, according to the SQLDeveloper Help :
“The Unit Test Repository is a set of tables, views, indexes and other schema objects…”

Look, don’t panic. Despite this apparently database-centric architecture, you do not have to deploy your test code along with your application code base to execute your tests. Remember, as with all the mainstream Oracle IDEs, SQLDeveloper allows you to be connected to different databases simultaneously.
Added to this, it would appear that a significant element of the SUT architecture involves XML configuration files. This would also explain the lack of any requirement to mess about with database links to get all this stuff to work.

Repository Pre-Setup Tasks

The first design decision I made was that I wanted to keep any testing infrastructure entirely separate from my Application Code.
Therefore, I’ve created a separate database schema – TEST_REPO – specifically to hold the Repository.

Whilst it’s perfectly possible to have SQLDeveloper guide you through all of the setup work, this does require that you connect as a highly privileged user (SQLDeveloper tends to ask for the SYS password).
That’s not a problem if, like me, you’re just messing about on your own copy of Oracle Express Edition. If you’re in a more formal environment however, you may well need to provide your DBA with a script.

This should do the job (runnable in SQL*Plus) …

-- Script to create the roles required for a SQLDeveloper Unit Testing Repository
-- and a schema to host the repository
set verify off
define repo_owner = test_repo
define repo_default_ts = users
define repo_temp_ts = temp
accept passwd prompt 'Enter a password for the new schema [] : ' hide

-- Setup Roles
create role ut_repo_administrator;
grant create public synonym,drop public synonym to ut_repo_administrator;
grant select on dba_role_privs to ut_repo_administrator;
grant select on dba_roles to ut_repo_administrator;
grant select on dba_tab_privs to ut_repo_administrator;
grant execute on dbms_lock to ut_repo_administrator;

create role ut_repo_user;
grant select on dba_role_privs to ut_repo_user;
grant select on dba_roles to ut_repo_user;
grant select on dba_tab_privs to ut_repo_user;
grant execute on dbms_lock to ut_repo_user;

-- Create Schema to host Repository
create user &repo_owner identified by &passwd;
alter user &repo_owner default tablespace &repo_default_ts;
alter user &repo_owner temporary tablespace &repo_temp_ts;
alter user &repo_owner quota unlimited on &repo_default_ts;

-- System Privs
grant create session, connect, resource, create view to &repo_owner;

-- Role Privs
grant ut_repo_administrator to &repo_owner with admin option;
grant ut_repo_user to &repo_owner with admin option;

-- Object Priv
grant select on dba_roles to &repo_owner;
grant select on dba_role_privs to &repo_owner;

Note that you may want to change the the Repository Owner Schema and tablespace variables to values more suited to your environment.

Now we’ve created the new schema, we need to create a connection for it in SQLDeveloper.

In the Connections Navigator, click the Plus button …


Input the appropriate details in the New/Select Database Connection Window…


If you hit the Test button, you should see the Status set to “Success” in the bottom left-hand corner of the window.

Once it’s all working, hit the Save button, to retain the connection, then hit Connect to logon as TEST_REPO.

Now that’s all done we’re ready for the next step…

Creating the Repository

From the Main Menu, select View/Unit Test

A new, but currently quite boring navigator window will open up under the Connections Navigator :


From the Main Menu, select Tools/Unit Test/Select Current Repository…

In the Repository Connection window, select the test_repo connection we’ve just created…


…and hit OK. This should give you :


Just say Yes.

After a brief interlude, during which SQLDeveloper does an impression of a Cylon from the original Battlestar Galactica…


By your command !

you will get..


If you now have a look in the Object Browser for the test_repo user, you’ll see that SQLDeveloper has been quite busy…


Next up…

Granting Access to the Repository

From the Tools menu select Unit Tests then Manage Users…

When prompted for the connection to manage users, I’ve chosen TEST_REPO as it’s the only one that currently has admin rights on the repository.

The Manage Users Window that appears has two tabs, one for Users and one for Administrators :


I’ve added the owner of the application code that I’ll be testing to keep things simple.

The result is that, if I now connect as FOOTIE ( the application owner), I should be able to start adding some tests.

Naming Conventions

The SQLDeveloper Help has something to say on naming tests, which you may want to consider.
I’ve simply gone down the route of using the format package_name.procedure_name_n.
I want to group the unit tests at the same level as the programs for which they are written, so the Test Suites I’ll be creating are grouped by (and named for) the packages that the tests run against.

One aspect of object naming that I haven’t formalised in this post is that of items that I’ve added to the Library. This is an area to which you may well want to give some consideration.

Testing Insert Statements

The first set of tests I need to write centre around inserting data into tables. To cover some of the scenarios we might commonly encounter when performing this sort of operation, I’ll be looking at adding records to Tables related to each other by means of Referential Integrity constraints.

My First Test – Inserting a Record into a table with a Primary Key

I’ve got a User Story about adding Competitions to the application. The Story has two Acceptance Criteria :

  1. A new competition can be added
  2. A Competition cannot be added more than once

Here’s a quick reminder of what the COMPETITIONS table looks like :

create table competitions
    comp_code varchar2(5) constraint comp_pk primary key,
    comp_name varchar2(50) not null,
    description varchar2(4000)

As we’re trying to follow the approach of Test Driven Development and write our tests first, we just have a stub PL/SQL procedure to run the initial tests against :

create or replace package body manage_competitions

    procedure add_competitions
        i_code competitions.comp_code%type,
        i_name competitions.comp_name%type,
        i_desc competitions.description%type default null
    end add_competitions;
end manage_competitions;

Time to meet…

The Create Unit Test Wizard

As this is my first test, I’m going to go through it step-by-step here. After that, the novelty will quickly wear off and I’ll just reference the steps or the Test Process Stages as appropriate.

In the Unit Test Navigator, right-click the Tests node and select Create Test…


This will bring up the Create Unit Test Wizard


Select the Connection for the Application Owner (FOOTIE, in this case) from the Connections drop-down…


…and navigate the tree to the procedure we want to test – i.e. manage_competitions.add_competition…


And now click Next

This brings us to step 2 of the Wizard, specifying the Test Name. In line with my standard, I’m calling this one MANAGE_COMPETITIONS.ADD_COMPETITION_1.
Leave the Radio Group selection as Create with single Dummy implementation


Click Next

I’m not going to create a Setup Process in this test for the moment. If you did want to, then you’d click the Plus button and…well, we’ll return to that later.
Anyway, step 3 looks like this :


Click Next

Now we need to specify the input parameter values that we’ll be using in the test.
The values I’m going to use are :

  • I_CODE – UT1
  • I_NAME – Test1
  • I_DESC – Test competition

Leave the Expected Result as Success


Click Next

…which takes us to…


Click the Plus to add a Process Validation and a drop-down will appear.
Scroll through this and select Query returning row(s) :


…and you’ll be presented with…


We now replace the default SQL statement with one that checks that we’ve inserted our record successfully.
Rather than hard-coding the COMP_CODE value we’ve input into the procedure, we can use SUT’s substitution syntax, thus inheriting the value of the I_CODE input parameter we specified back in Step 4. The code we’re going to add is :

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

Note that the parameter names appear to be case sensitive. If it’s entered in lowercase, SUT will complain at runtime.
The end result looks like this :


Click OK to dismiss the Process Validation Window.

Back on the Wizard page, click Next

Finally, we need to specify the Teardown Process – i.e. code to return the application to the state it was prior to the test being run.


Hit the Plus button again and you will get a slightly different drop_down. This time, we want User PL/SQL Code


You’ll now get the Teardown Process Window. As we’ve only inserted a single row as part of this test, we can simply rollback the transaction to put things back as they were. This being a PL/SQL block, the code is :



Click OK to dismiss the Window.
Now click Next to get to the Summary Page, which should look something like…


Once you’re happy with the Summary, click Finish.

You should now see the new test in the Unit Test Navigator :


Click on the new test and you will see :


We can now run the test by clicking the Run Button. This will cause the Test Pane to switch to the details tab where the results of our first execution will be displayed :


As you’d expect, because the program we’re calling doesn’t do anything, the test validation fails.

Before we go any further, there are a couple of things we probably want to do with this test.
First of all, we really should have code that ensures that we won’t be trying to insert a value that already exists in this table.
Secondly, it’s quite likely that we’ll want to reuse some of the test code we’ve written in future tests.

OK, first of all then …

Dynamic Value Queries

In my previous attempt to write Unit Tests in anonymous PL/SQL blocks, it was relatively simple to set an appropriate value for the Primary Key of the record we wanted to insert programmatically.
Obviously, this is not the case with the Unit Test we’ve just written.

Fortunately, SUT allows you to populate the call parameters with values from what it calls a Dynamic Value Query.

In the SQLDeveloper help, the method demonstrated for setting up a Dynamic Value Query, involves creating a table to to hold the values and then querying that at runtime.
In this instance, we don’t need a table to determine the parameter values we need to use.

In the Details tab of the test, we need to click the Dynamic Value Query pencil icon :


This will bring up the Dynamic Value Query window.

This code should do the job for now…

-- Make (reasonably) certain that the comp_code value we're trying to create
-- does not already exist in the table.
with suffix as
    select max( to_number( substr( comp_code, regexp_instr( comp_code, '[[:digit:]]')))) + 1 as numeral
    from 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) as I_CODE,
    'Test1' as I_NAME,
    'A test' as I_DESC
from suffix
where numeral is not null

…so I’ve dropped it into the Dynamic Value Query…


Click OK and we’re ready to re-run the test to check that our changes have worked.

Press the Run button and SQLDeveloper will ask if you want to save the changes you’ve just made.


We do.

The test will still fail ( we’re still running against a stub, remember), however, we can see from the test results that the Dynamic Values Query has generated the input values we were expecting :


NOTE – whilst the Dynamic Values Query we have written here will (eventually) work as expected, there is rather more to them than meets the eye. This is mainly because they are executed before any other step in a test. This can be problematic when a Dynamic Values Query needs to rely on values that are set in a Test’s Startup Process. There is a solution to this however, which I’ll cover later on.

Adding Code to the Library

We’ve written three distinct bits of code for our first test which we’ll probably want to use again.

SUT provides the Library as a means of storing such code for re-use in other tests.

Looking at our test in SQLDeveloper, the first code we see is the Teardown Process.

To add this to the library, click the Pencil icon to bring up the Teardown Process code window.

In the field at the bottom, we can now give this code a name and then hit the Publish button to send it to the library :


You should now see that the label we’ve given this code (rollback_transaction, in this case) appears in the Library drop-down at the top of the Window :


Now click the OK button to dismiss the window.

We can repeat this process for our Dynamic Values Query ( labelled add_new_competition) and our Validation ( competition_row_added).

If we now expand the Library node in the Unit Test pane, we can see that our code appears in the Library :


This should mean that creating our second test requires a little less typing…

Testing for an Exception

Our second test is to check that the Application does not allow us to add a duplicate COMPETITIONS record.

Opening the Wizard again, we select the same package and procedure as for our first test.

Following my naming convention, this test is called MANAGE_COMPETITIONS.ADD_COMPETITION_2.
This time, we do need a Startup Process to make sure that the record we attempt to insert already exists.
So this time, hit the Plus button on the Startup Process step and select User PL/Sql Code from the drop-down.

In the Startup Process Code Window, we’re going to add the following PL/SQL code :

    merge into competitions
    using dual
    on (comp_code = 'UT1')
    when not matched then
        insert ( comp_code, comp_name, description)
        values( 'UT1', 'Test', null);

Essentially, we want to begin by making sure that a competition with a COMP_CODE value of ‘UT1’ exists in the table.

We’ll probably need to do this again somewhere down the line, so I’ve added it to the library as setup_competition_UT1.

For this test, in the Specify Parameters step, we can type in the parameters manually. Our Setup Process ensures that a record exists and our test execution is to try to insert this record again. The parameter values we enter are :

  • I_CODE = UT1
  • I_NAME = Test
  • I_DESC – leave as null

This time, the expected result should be changed from Success to Exception.
We’re expecting this call to fail with “ORA-00001: unique constraint violated”.
Therefore, we need to set the Expected Error number to 1.


Our test will fail unless it encounters the exception we’ve specified when we call the procedure.
Given this, we don’t need to add a separate validation step.

We will be needing a Teardown Process however.
As before, we’re going to choose User PL/Sql Code from the drop-down.
This time however, we won’t have to type anything in to the window.
Instead, click on the library drop-down at the top of the code window :


… select rollback_transaction from the drop-down, and then check Subscribe


By subscribing to this code snippet, we “inherit” it from the library. Therefore it cannot edit it directly in the test.
If we did want to “tweak” it for the purposes of this specific test, we could hit the Copy button instead of subscribing.
This would copy the code block into the current test where it would then be editable for that test.

The test Summary looks like this :


As expected, when we execute this, it fails because the expected exception was not raised :


Now we have both of our tests written, we need to write some application code to get them to pass.

create or replace package body manage_competitions
	procedure add_competition
		i_code competitions.comp_code%type,
		i_name competitions.comp_name%type,
		i_desc competitions.description%type default null
        insert into competitions( comp_code, comp_name, description)
        values( i_code, i_name, i_desc);
	end add_competition;
end manage_competitions;

Now when we re-execute our tests, we can see that they pass :




Note that the latest test runs appear at the top of the Results listing.

Removing Old Test Results

If you want to remove an older result you can do this by right-clicking on it in the Results Tab and selecting Delete Result….

You can also clear down all results for a test by right-clicking in the Unit Test pane and selecting Purge Test Results…


This will bring up the Purge Test Results dialogue which gives you the option to remove all results for the test, or just those from before a given time and date.


NOTE – if you have a Test or a Suite with it’s Results tab showing when you purge, then they may not disappear immediately.
If this is the case, just click the Refresh button on the Test toolbar.

Creating a Test Suite

The development process around an application such as ours, will revolve around the PL/SQL package as the atomic unit of code.
Even though packages are containers for specific procedures, functions etc, it’s at the package level that the code is deployed to the database and therefore, it’s at that level it’s stored in Source Control.
So, when a developer needs to make a change to a package, we want them to be able to checkout and run all of the tests for the package.
SUT allows us to group our tests by means of a Test Suite.

So, we’re going to create a Suite for the MANAGE_COMPETITIONS package so that we can group the tests we’ve just created, and add more tests to it later on.

In the Unit Test tree, right-click on Suites and select Add Suite…


In the Add Suite dialogue that appears, enter a name for the Suite.


The new suite now appears in the Unit Test tree.
Now we need to add our tests to it.

Expand the tree under the new test suite, right-click on Tests and select Add Test…


Now select the tests you want to add from the list that appears in the Add to Suite Window :


…and click OK.

Notice that, although the tests have been added to the Suite, they still appear under the Test Node in the tree.
This node is going to get fairly crowded as more tests are added. This is one reason that a sensible naming convention is quite useful.

If we now run our new suite, we can see that all tests in the suite will run :


There is more to Test Suites than simply grouping tests together, but more on that later.

Thus far, we’ve covered similar ground to the Tutorial exercise in the SQLDeveloper Help, although it’s fair to say we’ve taken a slightly different route.

We’re now going back to a time when Scotland ruled the world in order to test…

Adding data to a “child” table

The Acceptance Criteria for the Add a Tournament Story are :

  1. A tournament can be added
  2. A tournament cannot be added for a non-existent competition
  3. The competition that this tournament is for must be specified
  4. The same tournament cannot be added for a competition more than once
  5. If specified, the year the tournament begins cannot be greater than the year that it ends

The stub of the code we’ll be testing is…

create or replace package manage_tournaments
	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
end manage_tournaments;

create or replace package  body manage_tournaments
	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
	end add_tournament;

…and the table DDL is…

create table tournaments
    id number constraint tourn_pk primary key,
    comp_code varchar2(5),
    year_completed number(4) not null,
    host_nation varchar2(100),
    year_started number(4),
    number_of_teams number(3) not null,

    constraint tourn_uk unique( comp_code, year_completed, host_nation)

alter table tournaments
    add constraint tourn_comp_fk foreign key
        (comp_code) references competitions(comp_code)

Hmmm, does something there look odd to you ? We may come back to it in a while.
First though, let’s write the tests for…

Variable Substitutions using binds

Our first test is wittily and originally entitled MANAGE_TOURNAMENTS.ADD_TOURNAMENT_1.
It’s simply testing that we can legitimately add a TOURNAMENT record to our application.

For the Test Startup, we need to make sure that we have a COMPETITION record to assign the tournament to so I’ve subscribed to the setup_competition_ut1 that we added to the Library earlier.

As for the call to the package, the parameters I’m using are from the very first International Football Tournament – the British Home Championships ( won by Scotland ) :

  • I_CODE = UT1
  • I_YEAR_END = 1884
  • I_TEAMS = 4
  • I_HOST = null
  • I_YEAR_START = null

The Validation Process is a Boolean function. Now, I had a few issues with the I_HOST replacement as a string (possibly because I passed in a parameter value of null in the test).
Fortunately, you can reference parameter values as bind variables…

    l_count pls_integer;
    l_host tournaments.host_nation%type := :I_HOST;
    select count(*)
    into l_count
    from tournaments
    where comp_code = '{I_CODE}'
    and year_completed = {I_YEAR_END}
    and nvl(host_nation, 'MIKE') = nvl(l_host, 'MIKE');

    return l_count = 1;

I know I’ll need to use this again to verify the test for adding a duplicate tournament so I’ve saved it to the Library as single_tournament_record_exists.

The teardown process is also taken from the library (a simple rollback once again).

We’ve got a database and we’re not afraid to use it !

The test to ensure that you can’t add a TOURNAMENT for a non-existent COMPETITION is more about the Data Model than the code. What we’re actually testing is that the Foreign Key from TOURNAMENTS to COMPETITIONS is in place and working as expected.

It follows a similar pattern to the very first test we created for adding a competition.
Indeed, the Dynamic Values Query looks rather familiar :

with suffix as
    select max( to_number( substr( comp_code, regexp_instr( comp_code, '[[:digit:]]')))) + 1 as numeral
    from 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) as I_CODE,
    1884 as I_YEAR_END,
    4 as I_TEAMS,
    null as I_HOST,
    null as I_YEAR_START
from suffix
where numeral is not null

Whilst the hard-coded names and parameter values reflect the fact that we’re calling a different procedure, the code to derive the I_CODE parameter value is identical.

Whilst we could add this query to the Library then copy it where we needed to and make necessary changes in each test, there is an alternative method of reuse we might consider. We could create a database function in the Repository to return the desired value for I_CODE.

NOTE – there are several considerations when determining whether or not this is a route that you wish to go down in terms of your own projects. However, in this instance, my Test Repository is being used for a single application, and by doing this, I’m ensuring that this code only needs to be written once.

We’re going to create this function in the TEST_REPO schema.
Before we do that though, we need to grant access to the COMPETITIONS table to TEST_REPO. So, connected as FOOTIE :

grant select on competitions to test_repo

Then, as TEST_REPO :

create function get_new_comp_code
    return varchar2
    l_comp_code varchar2(5);
    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;

Yes, in the Real World you may very well do this as part of a package rather than as a stand-alone function.

Next, we need to grant privileges on the function to the Repository Role :

grant execute on get_new_comp_code to ut_repo_user

The Dynamic Values Query can now be written to use the new function :

select test_repo.get_new_comp_code as I_CODE,
    1884 as I_YEAR_END,
    4 as I_TEAMS,
    null as I_HOST,
    null as I_YEAR_START
from dual

We expect this test to fail with ORA-2291 – Integrity Constraint violated.

Multiple Processes for a single Testing Stage

To test that the application will not allow the addition of a duplicate TOURNAMENT we need to make sure that records exist in two tables rather than one – i.e. a “parent” COMPETITIONS record and a “child” TOURNAMENTS record, which we’ll be attempting to duplicate.

Now we could do this in a single Startup Process. However, if we do at as two separate steps then we can save both to the Library and get a greater degree of re-use out of them. So, resisting the distant memory of Forms 2.3 Step Triggers and the feeling that you’re coding like it’s 1989…

On the Specify Startup Step of the Wizard, click the Plus button, add the first Startup Process ( i.e. ensure that the parent COMPETITIONS record exists).
Once you’re done with that, hit the Plus button again :


Select User PL/Sql code from the drop-down and then enter the following :

    merge into tournaments
    using dual
        comp_code = 'UT1'
        and year_completed = 1916
        and host_nation = 'ARGENTINA'
    when not matched then
            id, comp_code, year_completed,
            host_nation, year_started, number_of_teams
            tourn_id_seq.nextval, 'UT1', 1916,
            'ARGENTINA', null, 4

Incidentally we’ve moved on a bit in terms of test data and are now using details from the first Continental Tournament – the Copa America.
I’ve added this to the library as setup_tournament_for_UT1.

After all that, you should end up with something like …


The remaining tests don’t present anything new so we come to the point where we need to get the code to pass.
At this point you might be confident that this will do the job…

create or replace package body manage_tournaments
	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
        if i_year_start is not null then
            if nvl(i_year_end, i_year_start) < i_year_start then
                raise_application_error( -20000, q'[A tournament cannot end before it has begun...unless you're England !]');
            end if;
        end if;
		insert into tournaments
			id, comp_code, year_completed,
			host_nation, year_started, number_of_teams
			tourn_id_seq.nextval, i_code, i_year_end,
			i_host, i_year_start, i_teams
	end add_tournament;
end manage_tournaments;

Right, let’s test it shall we ?

When Data Models go bad…

I’ve added all five of the tests I’ve written to the MANAGE_TOURNAMENTS Test Suite so, I’m going to use it to execute my tests…

In the Unit Test Pane, expand the Suites node and click on the Suite we want to run. This will bring up the details of the Suite :


Now press the Run button and…not everything went as expected.

By collapsing the result tree, we can see which Test (or Tests) failed :


I think you noticed the problem a bit earlier. Someone has been a bit remiss when doing the data modelling. the COMP_CODE column is missing a NOT NULL constraint.

The Data Modeller claims that this was a deliberate oversight so we can see the value of testing the Data Model and not simply the code. Yeah, right.

Once we address this :

alter table tournaments modify comp_code not null

… and re-run the offending test on it’s own to check that it passes, we can then re-run the entire suite as a regression test.
Looks like the change hasn’t broken anything else ( the failure at the bottom is just the previous run result):


Summary of the Insert Tests

So far, we’ve found out how SUT is put together as a Testing Framework.

The tests follow the expected four stage pattern – Setup, Execute, Validate, Teardown, but of these only the Execute stage is mandatory.
Multiply Processes are permissible in any stage apart from Execute.
The facility to group tests into Test Suites is present, as you would expect.

We’ve also started to explore some of the specifics of SUT.

Pleasingly, it handles expected Exceptions with the minimum of input down to the level of the expected error code.

The Dynamic Values Query provides a means of generating conditional test input criteria.
The Variable Substitution syntax makes validation of tests executed with such generated values somewhat more straightforward.

If and when limitations of SUT are encountered then you always have the option of extending it’s capabilities using custom PL/SQL Stored Program Units.
I can see that you’re still a bit dubious about that last point. OK, I’m sure we’ll come back to that later( mainly because I’ve already written that bit !)

In the meantime, let’s see what we can learn from the tests for the second DML activity we’re looking at…

Testing Deletes

As with any DML action, how well an application handles Deletion of records is crucial in terms of how well it ensures data integrity.
With that statement of the obvious out of the way, let’s take a look at how SUT can help us with testing this aspect of our Application’s functionality.

Some of the tests we need to write require records to be present in the TOURNAMENT_TEAMS table, which looks like this :

create table tournament_teams
    tourn_id number,
    team_name varchar2(100),
    group_stage varchar2(2),

    constraint team_pk primary key (tourn_id, team_name)

alter table tournament_teams
    add constraint team_tourn_fk foreign key
        (tourn_id) references tournaments(id)

The thing about Dynamic Value Queries

The Acceptance Criteria we want to test for in the User Story for deleting a tournament are :

  1. A Tournament can be deleted
  2. A Tournament cannot be deleted if there are Teams assigned to the Tournament

I’ve created a stub procedure in the MANAGE_TOURNAMENTS package.
The appropriate signature has been added to the package header and the procedure in the body currently looks like this :

	procedure remove_tournament( i_id tournaments.id%type)
	end remove_tournament;

Yes, using the Natural Key for a TOURNAMENT record as input into this procedure would have made life easier in terms of writing the test. However, I feel quite strongly that we should not be making compromises in the Application Code to accommodate any Testing Tools we may need to use.

To ensure that our test is completely standalone, we may need to create both a COMPETITIONS and a TOURNAMENTS record in the Test Startup.
We then need to find out what the ID value is of the TOURNAMENT record we want to delete.
No problem there. We can re-use the routines we already have in the Library to Create the UT1 Competition and associated tournament.
Then we just need to use a Dynamic Values Query to retrieve the ID – something like :

select id as I_ID
from tournaments
where comp_code = 'UT1'
and year_completed = 1916
and host_nation = 'ARGENTINA'

Nothing to see here, right ? Well, if you had taken a look at the SQLDeveloper Help Dynamic Value Queries you would have noticed that…

“A dynamic value query is executed before the execution of all implementations in a test, including any startup action for the test. If you must populate a table before a dynamic value query is evaluated, you can do this in the startup action for a suite that includes the test.”

Fortunately, if you’re not entirely sure about how your test is going to work, you can click the Debug button and get details of the execution steps of the test or, in this case…


So, it would appear that the solution to this particular problem is to wrap this test in it’s own Test Suite.
This way, we can run the Startup Processes at the Suite level instead of the the Test level to ensure that they execute before the Dynamic Values Query.

So, I’m going to create a suite called manage_tournaments.remove_tournament_ws – for Wrapper Suite
As well as the Startup Processes, I’ll also move the Teardown Process from the test to this Suite.
Then, I’ll allocate the test to the Suite.

Creating the Wrapper Suite

This is the same process for creating any other Test Suite – i.e. Go to the Unit Test Navigator, right-click on the Suites node and select Add Suite… from the menu.


If we now bring up our new Suite, we can add the Startup and Teardown processes in the same way as we do for a test (i.e. hit the Plus button and away you go).
Once that’s done, we need to assign a test to the Suite.
Once again Hit the Plus button, this time in the Test or Suite Section to bring up the Add to Suite dialogue :


Select the test we want to add, in our case, MANAGE_TOURNAMENTS.REMOVE_TOURNAMENT_1 and make sure that the Run Test Startups and Run Test Teardowns are unchecked :add_to_suite2

Click OK and…


Even though we’ve specified that the Startup and Teardown Processes should not be executed when the test is run within the Suite, it’s probably a good idea to go back and remove them, if only to save much confusion later on.

Anyhow, when we now execute the suite we can see that the results are what we’re expecting and that, reassuringly, the generated synthetic key value ( I_ID) is being passed in :


Running Startup Processes in the Suite and the Test

To make sure that the Foreign Key from TOURNAMENT_TEAMS to TOURNAMENTS is working, we need to insert a TOURNAMENT_TEAMS record for the appropriate TOURNAMENT.ID as part of the Setup Process.

As with the previous tests, we’re going to need to include this in the Wrapper Suite we’ve just created so that the Dynamic Values Query to get the ID value works.

Hang on, let’s consider that decision for a moment.

It is true that the second test will require the same Startup Processes that we have in our existing Wrapper Suite for the first test. It will also need these Startup Processes to be executed in a Wrapper Suite as it needs to have access to the TOURNAMENTS.ID value in a Dynamic Values Query.

To a programmer, it’s logical therefore that the second test should be allocated to the same Test Suite as the first as the code has already been written there and there’s absolutely no need to go duplicating effort ( even if it is mainly just importing stuff from the Library).

Of course, we will need to “move things about a bit” to make sure that both tests can run properly within the same suite. For example, we need to perform the “successful” delete test last as the test for an Exception is relying on the record to be deleted…er…not being deleted when it runs.

To a tester, things may appear a little different. One of the principles of Unit Testing is to make sure that, as far as possible, that tests can be run independently of each other.

It is for this reason that you should give serious consideration to creating a separate Wrapper Suite for our second test.

The alternative, as I’m about to demonstrate, gets a bit messy…

So for our new test, as well as the Setup Processes in the Suite, we’ve also included one in the test for the addition of the TOURNAMENT_TEAMS record.
The creation of the TOURNAMENT_TEAMS record needs to remain in the Test Startup Process rather than in the Suite as it’s only relevant to this test and not for all tests in the Suite. However, as the TOURNAMENT record we’re looking for will definitely have been created by the Wrapper Suite Startup Processes – before the Test Startup Process fires, this should not be a problem.

So, the main differences between this test – MANAGE_TOURNAMENTS.REMOVE_TOURNAMENT_2 – and it’s predecessor are simply that we are expecting this test to error with ORA-2292 – “Integrity constraint violated” – and that we now include the following Startup Process code to create the TOURNAMENT_TEAMS record :

    l_id tournaments.id%type;
    select id
    into l_id
    from tournaments
    where comp_code = 'UT1'
    and year_completed = 1916
    and host_nation = 'ARGENTINA';

    merge into tournament_teams
    using dual
        tourn_id = l_id
        and team_name = 'URUGUAY'
    when not matched then
    insert( tourn_id, team_name, group_stage)
    values(l_id, 'URUGUAY', null);

Now things start to get a bit complicated. In order to make sure that the test for a legitimate delete does not fail, we need to “teardown” the child record in TOURNAMENT_TEAMS that we created in our Startup Process. Well, no problem…except that SUT does not appear to allow Variable Substitution syntax to be used in a Teardown Process.
Therefore, we need to indulge in a little light hacking and put the following code in a Validation Action in our test :

    l_id tournament_teams.tourn_id%type := :I_ID;
    delete from tournament_teams
    where tourn_id = l_id
    and team_name = 'URUGUAY';

This time, when we add the test to the Wrapper Suite, we make sure that the Test Startups are run :


Finally, we need to make sure that our new test runs first in the suite. In the Test Suite listing, click on the Test name then click the blue up arrow…


…until our test is at the top of the pile…


Once we’re happy with our tests, we can then fix the application code :

procedure remove_tournament( i_id tournaments.id%type)
    delete from tournaments
    where id = i_id;
end remove_tournament;

…and run our wrapper suite to make sure everything passes…


Finally, we need to add the manage_tournaments.remove_tournaments_ws suite to the Suite we have for all the tests for the MANAGE_TOURNAMENTS package.
To do this, go to the Unit Test Navigator and expand the MANAGE_TOURNAMENTS suite.
Then, right-click the Suites node and select Add Suite…


Now select the manage_tournements.remove_tournaments_ws suite from the list …


…and click OK.

Near enough is good enough ? Finding a key value generated during a Startup Process

The other record deletion story we have concerns COMPETITIONS records.

The procedure we’ll be testing is in the MANAGE_COMPETITIONS package :

procedure remove_competition( i_code competitions.comp_code%type) is
    delete from competitions
    where comp_code = i_code;
end remove_competition;

The acceptance criteria, and indeed, the functionality, that we’re testing here is very similar to our previous User Story.
The Acceptance Criteria are :

  • A competition can be deleted
  • A competition cannot be deleted if a tournament exists for it

To make sure that the test to Delete a COMPETITION is self-contained, we need to make sure that the record we are deleting has no child records.
The easiest way to do this is to create the record as part of the Startup Process.
Obviously, this will need to referenced by a Dynamic Values Query and therefore this code will need to run in another Wrapper Suite.

Once again, I’m using the GET_NEW_COMP_CODE function I created earlier. Yes, that one that you weren’t sure about. The one that you’re probably still not sure about. The Startup Process in my Wrapper Suite will be User PL/Sql Code :

    l_code competitions.comp_code%type;
    l_code := test_repo.get_new_comp_code;
    insert into competitions(comp_code, comp_name, description)
    values( l_code, 'Test', null);

The next step may well be a bit tricky – in the Dynamic Values Query we use to determine the parameters to pass to the procedure, we need to find the COMP_CODE created in the Startup Process.
Now, we can do something like this…

select 'UT'
    || to_char(substr(test_repo.get_new_comp_code, regexp_instr( test_repo.get_new_comp_code, '[[:digit:]]')) -1) as I_CODE
from dual;

…but if the table has a change made to it in another session in the interval between our Startup Process and our Dynamic Values Query executing then we may well end up using an incorrect COMP_CODE value.

Let’s stop and think for a moment.
What we are writing here is not application code that may be executed concurrently by multiple users. We are writing Unit Tests.
Therefore, whilst this potential inexactitude would be problematic within the core Application Codebase, it’s not so much of an issue for a Unit Test.
Remember, the Unit tests will probably be run on a Development Environment with few users ( i.e. Developers) connected. They may also be run on a Continuous Integration Environment, in which case they are likely to be the only thing running on the database.
OK, so I could do something clever with the COMP_CODE value used in the Startup Process being assigned to a package variable/temporary table/whatever for reference by later testing steps in the same session, but I’m really not sure I need to go to all that effort right now.
You may well disagree with this approach, but as I’m the one at the keyboard right now, we’re pressing on…

The validation code for this test will be a Query returning no row(s) :

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

The final Test (inside it’s wrapper suite), looks like this :


By contrast, making sure that we can’t delete a COMPETITIONS record which has TOURNAMENTS records associated with it is pretty straightforward.
We simply use the MERGE statements we’ve already added to the library to make sure we have a COMP_CODE UT1 and test against that.
As we know the value that we want to pass in ahead of time, we don’t even need a Dynamic Values Query. Therefore, we don’t need another Wrapper Suite.

The test ends up looking like this :

Having checked that they work as expected, I’ve added the second test and the wrapper suite for the first test to the MANAGE_COMPETITIONS suite.
Together with our earlier tests for this package, the Suite as a whole now looks like this :


Deletion Tests Summary

By using the ability to define Startup (and Teardown) Processes at Suite level, we can workaround some of the limitations of Dynamic Values Queries.
Additionally, this property of Suites offers some additional flexibility within SUT.
This does mean that some test code may end up in parts of the test structure where you would not normally expect to find them.


There doesn’t seem to be anything too different about the way SUT lets you test Update operations, except for the facility to have multiple implementations of a single test. There’s an example of this in the Testing Tutorial in the SQLDeveloper Help. Alternatively…

Multiple Implementations of the same test

We’ve got a Story about updating TOURNAMENTS records.

The Acceptance Criteria are :

  • Change the number of teams taking part in a tournament
  • Change the year a tournament started
  • The year a tournament started cannot be after the year a tournament finished

The procedure that we’re testing is in the MANAGE_TOURNAMENTS package :

procedure edit_tournament
    i_id tournaments.id%type,
    i_teams tournaments.number_of_teams%type default null,
    i_year_start tournaments.year_started%type default null
    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;

We’ve also added a check constraint to the table :

alter table tournaments add constraint chk_end_after_start
    check(nvl(year_started, year_completed) <= year_completed)

Once again, as the procedure we’re testing requires an ID value that we may or may not be creating at runtime, we’ll be needing a Wrapper Suite to feed a Dynamic Values Query to generate the TOURNAMENTS.ID value we pass into the procedure we’re testing.

Once we’ve got our Suite – which I’ve called manage_tournaments.edit_tournament_ws, we can start looking at the Test.

The Dynamic Value Query for the first test is :

select id as I_ID,
    5 as I_TEAMS,
    null as I_YEAR_START
from tournaments
where comp_code = 'UT1'
and year_completed = 1916
and host_nation = 'ARGENTINA'

I’ve published this to the Library as edit_tourn_params_ut1 as we’re going to need variations of it shortly.
The Expected Result is SUCCESS.

The Process Validation is a Boolean Function, which I’m adding to the library as verify_edit_tournament :

    l_id tournaments.id%type := :I_ID;
    l_teams tournaments.number_of_teams%type := :I_TEAMS;
    l_year_start tournaments.year_started%type := :I_YEAR_START;
    l_count pls_integer;
    select 1
    into l_count
    from tournaments
    where id = l_id
    and number_of_teams = nvl(l_teams, number_of_teams)
    and nvl(year_started, year_completed) = coalesce(l_year_start, year_started, year_completed);

    return l_count = 1;

If we expand our new test in the Unit Test Navigator we can see that we have something called Test Implementation 1 under it.


Each test can have multiple implementations, a feature we’re going to make the most of for the User Acceptance Criteria we’re dealing with now.
First thing to do then, is to rename Test Implementation 1 to something a bit more meaningful.

To do this, right-click on the Implementation and select Rename Implementation…


Then enter the new name and hit OK


Now we can create a second implementation by right-clicking the Test itself and selecting Add Implementation…


This time, I’ve called the implementation update_year_started.

We can now see that the new Implementation is in focus in the test, but that the Execution and Validation Processes have not been populated.impl_in_focus

I’ve copied in the Dynamic Values Query from the Library and made the necessary changes for this implementation…


…and subscribed to the verify_edit_tournament Boolean Function we created in the first implementation.

The third Implementation is called update_start_after_end and is the same as the second, except I’m passing in a year later than the current YEAR_STARTED value for the TOURNAMENTS record.
The Expected Result is Exception with an ORA-2290 Check Constraint violated error so there’s no need to include the validation function.

One point to note here is that the Implementations seem to execute alphabetically by name and I don’t see any way of changing this manually.
This is not an issue in this case, when each test is reasonably independent, but it’s worth bearing in mind.

Once all of the application code is in place, the Test Suite result looks like this :


Testing an In/Out SYS_REFCURSOR

Yes, we’re at the point that you may well have been dreading.
The Ref Cursor has been such a wonderfully useful additions to the PL/SQL language. It makes passing data between PL/SQL and programs written in other languages so much easier.
It is ironic therefore, that getting stuff out of a Ref Cursor is often quite painful when using a PL/SQL client.

Given this, one option we might consider when testing Ref Cursors could be to use whatever test framework is being employed to test the client code calling our PL/SQL API. However, that would be to pass up the opportunity to use Wouter’s clever little trick.

So, with nothing up my sleeves…

Is this a Ref Cursor that I see before me ?

The Acceptance Criterion for our User Story is that the application lists all tournaments in the system the specified competition.

The procedure we need to test is in the MANAGE_TOURNAMENTS package and looks like this :

procedure list_tournaments
    i_comp_code tournaments.comp_code%type,
    io_tourn_list in out SYS_REFCURSOR
    open io_tourn_list for
        select id, comp_code, year_completed, host_nation, year_started, number_of_teams
        from tournaments
        where comp_code = i_comp_code;
end list_tournaments;

The first clue to the fact that this test will be a bit different from normal comes when you select this procedure right at the start of the Create Test Wizard.
Immediately you will get :


In a way this is reassuring. SUT recognizes that we need to handle a REF CURSOR. However, the template Query it provides for the Dynamic Values Query we need to use appears to pose more questions than answers…

select ? as I_COMP_CODE,
    ? as IO_TOURN_LIST,
    ? as IO_TOURN_LIST$
from ?
where ?

Now, there may well be a way of getting this to work as intended, but I’ve not been able to find out what it is.
What we can do instead is a bit of light cheating…

Wouter’s Method

To start with, we need to create a procedure in the TEST_REPO schema. This will act as a dummy Test Execution so we can do the real testing in the Validation Process.

Still don’t like me creating my own objects in the Repository ? Well, fair enough, but in this case, I can’t see any other option.
The procedure then is :

create or replace procedure this_is_not_a_cursor
-- Been listening to a bit of Public Image Ltd, hence the name of this proc...

…look, you can put it in a package if that’ll make you feel any better about it.

Anyway, we need to grant execute permissions to the SUT roles :

grant execute on this_is_not_a_cursor to ut_repo_user
grant execute on this_is_not_a_cursor to ut_repo_administrator

Now, let’s try creating our test again. We’re using the FOOTIE connection as usual. However, this time, we’ll be selecting a program from the Other Users node…


The test name still follows our naming convention -i.e. MANAGE_TOURNAMENTS.LIST_TOURNAMENTS_1.

The Startup Process makes sure that we have records to query :

    procedure ins( i_year tournaments.year_completed%type,
        i_host tournaments.host_nation%type,
        i_teams tournaments.number_of_teams%type)
        merge into tournaments
        using dual
            comp_code = 'WC'
            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( tourn_id_seq.nextval, 'WC', i_year,
                i_host, null, i_teams);
    end ins;
    merge into competitions
    using dual
        on ( comp_code = 'WC')
        when not matched then
        insert( comp_code, comp_name, description)
        values('WC', 'World Cup', 'FIFA World Cup');

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


We don’t need to specify any input parameters for the Execution Step of our test so we can skip straight on to Process Validation. Here we define some User PL/Sql Code, where all the fun happens…

    l_rc sys_refcursor;
    rec_tourn tournaments%rowtype;
    l_count pls_integer := 0;
    l_exp_count pls_integer;
    -- The "real" test...
    manage_tournaments.list_tournaments('WC', l_rc);
        fetch l_rc into rec_tourn;
        exit when l_rc%notfound;
        l_count := l_count + 1;
    end loop;
    close l_rc;
    -- confirm that the correct number of records have been retrieved
    select count(*) into l_exp_count
    from tournaments
    where comp_code = 'WC';

    if l_count != l_exp_count then
        raise_application_error(-20900, 'Number of records in ref cursor '||l_count||' does not match expected count of '||l_exp_count);
    end if;

So, as we can use a PL/SQL block in Process Validation, we can define our SYS_REFCURSOR variable and execute the call to our procedure here.

Having added our “standard” Teardown, we’re ready to test.

The result….


The main drawback with this approach is that you now have a “customised” repository and will have to cope with any extra admin around administration and deployment of such objects. On the plus side, you can test Ref Cursor stuff.

Startup/Teardown using Table/Row Copy

Sooner or later you will encounter a testing scenario where a simple rollback just won’t do.
The next User Story is just such an example…

Bulk Upload Competitions – Using Table Copy for testing and rollback

This Story is intended to replicate the sort of ETL process that is quite common, especially in a Data Warehouse environment.
The scenario here is that you receive a delimited file containing data that needs to be loaded into your application.
The load needs to be permissive – i.e. you don’t want to fail the entire load if only a few records error.
The file format is validated as being what is expected by being loaded into an external table,
The load process then uses LOG ERRORS to upload all the records it possibly can, whilst keeping track of those records that failed by dumping them into an Error table.
The thing about LOG ERRORS is that it runs an Autonomous Transaction in the background.
Therefore, even you issue a rollback after the load, any records written to the error table will be persisted.
In light of this, we’re going to need to use something else for our Teardown process.

The Data Model

Just to quickly recap, we already have an external table :

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
            badfile 'competitions.bad'
            logfile 'competitions.log'
            skip 1
            fields terminated by ','
                comp_code char(5),
                comp_name char(50),
                description char(4000)
    reject limit unlimited

We also have a csv file – competitions.csv with the data to load (including a duplicate record) :

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

We have an error table – ERR$_COMPETITIONS – that’s been created by :


…and we have a stub we’ll be using to test the load (in the MANAGE_COMPETITIONS package

    procedure upload_competitions

When we create the test, the Startup Processes need to backup both the COMPETITIONS table and the ERR$_COMPETITIONS table.
Creating the first Startup Process, we select Table or Row Copy from the drop-down :


In the Window that pops up, the Source Table is the table we want to copy.
The Target Table is the temporary table that SQLDeveloper is going to create as a copy of the Source Table.
Note that the Target Table defaults to the same name irrespective of how many Startup Processes we specify.
For our first Startup Process, things look like this :


Notice that the Generated Query field updates as you enter the name of the source table. If you want to make sure that this Query is going to work at runtime, you can hit the Check button and (hopefully) be reassured with the message :


So, we’ve got a backup for the COMPETITIONS table, now we need one for the error table.
This is pretty similar to the first Startup Process except that this time we rename the Temporary Table to TMP$MANAGE_COMPETITIONS.UPLERR :


As with the startup, there are two validation processes required. Actually, you could probably do it all in one but that would be to pass up the opportunity to demonstrate both at work.

Both of these will be User PL/SQL Code blocks. First off, check that we’ve loaded the correct number of rows :

    l_count pls_integer;
    wrong_count exception;
    select count(comp.comp_code)
    into l_count
    from competitions comp
    inner join competitions_xt cxt
    on comp.comp_code = cxt.comp_code;

    if l_count != 7 then
        raise wrong_count;
    end if;

…and then make sure that we have the correct number of error records ….

    l_count pls_integer;
    wrong_count exception;
    select count(*)
    into l_count
    from err$_competitions;

    if l_count != 1 then
        raise wrong_count;
    end if;

Finally, for the Teardown, we need to restore our tables to the state prior to the test execution.

This time, the process type from the drop-down is Table or Row Restore

Note that we can check the check-box to drop the temp table pre-checked…


For the second Teardown process, to restore the Error table to it’s former state, we need to do a bit more typing.
This is because SQLDeveloper defaults to the same values for each Teardown Process.
So, we need to specify that our Target Table is ERR$_COMPETITIONS and our Source Table is “TMP$MANAGE_COMPETITIONS.UPLERR” :


After all that, we can see that we have two Startup Processes, two Validation Processes, and two Teardown processes in our new test :


After confirming that the test fails as expected…


…we update the application code…

procedure upload_competitions
    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;

…and re-run the test…


Sharing your Suites – version control and code promotion for Tests

If you plan to use your SUT tests in anything other than a single repository, chances are that you’ll want to be able to :

  • transfer them between environments
  • put them under some form of source control

Well, you’re in luck. Not only can you export Tests or Suites to an xml file on disk, an export will automatically include any Subscribed objects from the Library.
To demonstrate, right-click on the Suite we want to export :


…and select the file location…


…and SUT will anticipate your every need…exp_conf


Obviously, you’ll need to make sure you deploy any custom Stored Program Units upon which the exported objects are dependent.

To import a file into a repository, you can use the Main Tools/Unit Test menu :imp_menu

…which allows you to choose the file to import, as well as the option of whether or not to overwrite an object of the same name that already exists in the repository :imp_file_select


Overall, SQLDeveloper Unit Testing provides a number of helpful features to reduce the burden of writing and maintaining tests.
Notable plus points are :

  • being able to save code in a Library to promote re-use
  • table backup and restore functionality for test Startups and Teardowns
  • the seamless way that exceptions can be tested down to the level the error code
  • the fact that test results are retained and collated in the tool

Being declarative in nature, SUT provides a common structure for Unit Tests. Being declarative in nature, SUT does have some limitations.
It is possible to overcome some of these limitations by adding custom database objects to the Repository. Some consideration needs to be given as to what extent you want to do this.

I will be comparing SUT with other PL/SQL testing frameworks in a future post. Before that, I need to evaluate some other frameworks.
The next one on my list is utPLSQL…

17 thoughts on “(Almost) Everything you ever wanted to know about SQLDeveloper Unit Testing but were afraid to ask

  1. Pingback: utPLSQL – We’re building the…Unit Tests! | The Anti-Kyte

  2. Pingback: Testing Times – using ruby-plsql-spec for testing PL/SQL | The Anti-Kyte

  3. Thanks Mike for such a detailed explanation on Sql Developer Unit Testing, you got a good knack of explaining things, for which any novice can easily understand this and can try out on his own.

  4. Thanks for the detailed post Mike. It’s really useful. I have a question though. Is there a way to take a backup of the data in the test repository. This is applicable in scenarios where the test repository is created only in the development instance. So when the dev database gets cloned from Production, we would lose all data related to the repository. Is there a workaround to address this issue?


    • Vidya,

      You can always use a separate database to hold your repository. You could even use an Express Edition database and therefore refresh of the Development database would not be an issue.
      If all of the objects in your repository have been created using the SQLDeveloper Unit Testing (SUT) tool interface ( i.e. you don’t need any database objects that are external to the repository), then you can use the SUT export tool. Then you’d just need to re-create the schema that owns the repository in the Dev database after the refresh, then import the tests that you’ve exported beforehand.
      If you want to hang on to the actual data ( maybe test runs ?) then I’d suggest that a datapump export of the repository owner schema (in addition to the test exports mentioned above)would be the way to go.



  5. Thanks for this info Mike. i have a question here, Example :- i have created the TEST using a existing procedure and it’s succeed. Assume that, as per requirement i added new parameter for the same object. How can we pass the new parameter values to that test and even existing test showing the result as failed. Thanks in advance. Hopes my question is valid one.

    • Updendar,

      If you right-click the test and select Synchronize from the menu, you should be able to select the procedure from the tree. At this point, the test should pick up the extra parameters.



  6. Thanks for the detailed explanation Mike !!
    Question – We have multiple environments Dev, Test, UAT, etc.
    So, in our case, we create the UT Repository in the Dev Environment then for executing these tests in the Test/UAT environment – Do I need to create separate repositories for these i.e. whether each environment will require its own repository ?

    • Prasun,
      You can use one repository for multiple environments. There’s no reason a repository has to be in the same database as the one that you’re running the tests on.



      • Hi Mike,

        Thank you .

        How can i apply the tests in testing repository which is developed in DEV database to any other higher environments without actually moving the repository to higher environment database .

        Thanks and Regards,
        vamsi sreepada

      • Vamsi,

        As detailed in the article, you simply need to connect to the repo as that application owner in the environment you want to test in.
        Provided you’ve given the application owner the appropriate permissions as I describe, this should work.
        The only thing to look out for would be if you have any database objects acting as “helpers” for your tests.



      • Hi Mike,

        Thank you for your detailed explanation.

        I have one more question.
        As explained, same repository can be used in multiple environments.
        I want to run my tests in staging environment.But the repository which is there in Dev cannot be used in staging because of business restriction.
        Please let me know how can we move repository from one database to another i.e from dev to staging environment


      • Radha,

        there’s a couple of things here.
        If you look in the SQLDeveloper help you can find a section entitled “Exporting and Importing Unit Test Objects”.
        I’ve included something similar in this post under the heading “Sharing your Suites – version control and code promotion for Tests”.

        In short, if you can’t use your existing repository you’ll need to create a one in your target environment.
        You can then use the export/import utility to copy your tests/suites from the old repository to the new one.
        Alternatively, you may be able to export the repository in one fell swoop using Data Pump but I’ve not tried this.



  7. Hello Mike, I have a question. Can I use (refer) a variable from User PL/SQL that is in Startup Process, in Process Validation?
    For example, I store the count of records in a variable during Startup Process using User PL/SQL. Later, during Process Validation, can I use the same variable that I defined in Startup to compare the number of rows after Operation Call.

    • Ali,
      I think you may be able to do this, but only if you use a session level variable to store the result.
      Something like a context, or a package variable should remain visible for the duration of the test as all of the steps should normally execute in the same session.



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 )

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s

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