Skip to main content

Unit testing in database - Organization tips


Here is a link to previous article about Test patterns. Today you should learn how you actually run the tests and how to keep them organized.

How to run your tests

If you didn't figured this out last time, this is how you run your tests:
BEGIN;'PLAYER_UT');    -- run just specific package

And here is how the test package specification may looks like:


    -- %suite
    -- %rollback(manual)
    -- %beforeeach(before_each)
    -- %aftereach(after_each)
    -- %beforeall(before_all)
    -- %afterall(after_all)

    PROCEDURE before_all;
    PROCEDURE after_all;
    PROCEDURE before_each;
    PROCEDURE after_each;

    -- %test
    PROCEDURE test_create_player;

    -- %test
    -- %throws(PROGRAM_ERROR)
    PROCEDURE test_create_player#duplicite_name;

    -- %test
    PROCEDURE test_notifications;


Organization tips

You should have all tests independent on each other, don't depend on their order in the test package and you should do a preparation and cleanup before and after each test or whole package (called test suite). Thats where you can leverage before|after_all|each procedures defined in the test specification. I also like to keep the transactions in my control with the %rollback(manual) flag but unless you are dealing with autonomous transaction you don't need this feature.

The more tests you write, the more urge you will feel about organizing them in a meaningful way so you know from the run report whats wrong (and where), so you know where to find the tested procedure quickly.

My suggestions are:

  • for every package create just 1 test package with same name and UT postfix (or prefix)
  • for every module you want to test create at least 1 test procedure, followed by hash and scenario name
  • I also add test_prefix to each test procedure but I don't recommend that, you will run out of 30 characters name limit very quickly

Untestable long/legacy code

Sounds easy if you are just starting your new project. It is a bit harder to do when you are trying to write tests for a long legacy code you inherited from some guy who already left your company.

To safely refactor or redesign code (or even if you don't plan to touch it) you should build your tests first. You might be scared to write tests for complex procedures you have. Don't worry. Pick part of your procedure and write a test for that. Then pick another block in that procedure and test that. Or just stacks these tests in a single test to mimic your procedure.

Here are my tips:

  • identify logical blocks in your module and for each write your test (or just for the important parts of the module)
  • when you write tests covering 80% of your code you should be satisfied
  • if you have time, you can refactor the code without any worries, you now have your safety net

Sometimes it is not reasonable to clear and setup test data for every block in your module and you should not rely on the correct execution order. Also doing the proper cleanup might be expensive (too slow) and you want your tests to be very fast. What I do then is to stack multiple tests in single procedure. You can compare following procedure with simple version in the previous article.

    PROCEDURE test_create_player AS
        curr_player_name    players.player_name%TYPE;
        test_table_name     VARCHAR2(30);
        -- recreate player to be sure
        player.delete_player (
            in_player_id    => player.get_id(player_ut.player_name),
            in_silent       => TRUE
        player.create_player (
            in_player_name  => player_ut.player_name

        -- player is deleted in created during before_each procedure
        SELECT MIN(player_name) INTO curr_player_name
        FROM players
        WHERE player_id     = ctx.player_id()
            AND is_active   = 'Y';

        -- it would be good to check other tables
        -- like prepared chests, offers, rewards, battles, achievements, cards... @TODO
        SELECT MAX('PLAYER_DECKS') INTO test_table_name
        FROM player_decks
        WHERE player_id     = ctx.player_id()
        HAVING COUNT(*)     = 1;

Let's go through the code:

  • we delete player first to ensure he doesn't exist
  • then we create it from scratch and setup player_id in application context (ctx package)
  • here come the stack of 3 tests (ut.expect calls)
  • check if player exists in Players table
  • check if context value match
  • check additional resources exists (like the player_decks table)

This stacking saves me a lot of time and lot of duplicated lines and some server resources.

You may wonder why am I not just checking number of affected lines? It is about the error message in the test results when you run your tests. This way I will get the current and expected value right in the message.


I usually design my tests in a way that they can be run even on Production environment without affecting the real data (do the proper init and cleanup).

If you have autonomous transactions in your tests, you might wonder how to test that (and do the cleanup). You can:

  • store table content in a collection (or just ROWIDs) and after test delete anything what not belongs
  • store modified and new rows in a collection and delete them after test
  • retrieve recent sequence column from the table (if you have any) and delete anything above that number after test
  • backup whole table and restore it after test or use Oracle Flashback
  • just leave it there, it all depends on your needs

It also depends on concurrency. Do you have a server dedicated just for unit tests? Is it just bot testing on that server, you or the whole team? You need to ensure that you always starts your tests from the same point.


This probably looks like exhausting and maybe boring work. In the next and last article I will focus on unit tests gamification to keep it interesting.