PL/SQL Unit Testing package

I could have sworn I’d posted this a long time ago, but I can’t find it here.

An Apex friend mentioned his interest in a simple unit test package for Oracle PL/SQL.  I shamelessly copied and simplified a unit-testing framework that Steven Feuerstein published years ago.  His package was quite good and offered lots more features.  But the installation was the antithesis of light-weight and it was a pain to support in our (somewhat unique) environment.  The commercial version he developed for Quest is that much and more.

I simplified Feuerstein original version so that:

• It runs in our environment,
• The unit test scripts are fairly readable,
• It covered the basic unit tests I need for my work,
• Minimal test suite set up,
• Easily add more tests as the new function progressed
• Easily comment out obsolete tests that aren’t supported later in the development,
• Minimal overhead means the test scripts run fast,
• The code_tester package is extensible.

Disadvantages from the commercial package that Quest sells (developed by Feuerstein):

• It ain’t GUI
• Scripts are not modular, so it can get tedious when they get very large.

The attached zip file contains 2 files:

• The code_tester package itself (only 933 lines)
• test code_tester.sql – a sample test script that shows the various features in use (53 lines)

Usage

1. Compile the package,
2. Run the test code_tester script in your Oracle development tool of choice.  (I’m a fan of Allround Automations’ PL/SQL Developer, but it works fine in SQL*plus.)
3. Most of the tests are set up to succeed.  Change one so it will fail.  For example, change:

code_tester.ge('5 >= 5', 5, 5);

to:

code_tester.ge('5 >= 5', 5, 500);

It shows you which test(s) failed.  You can increase the amount of information reported, such as list the tests that passed as well as failed, count of failures, etc.

Output looks like:

FAILED    Test 'Value vs NULL, NULL NOT okay'  NULL NOT GREATER THAN 99999999
FAILED    Test 'Value vs NULL, NULL NOT okay'  NULL NOT GREATER THAN 29-SEP-2009 14:30:51
.
Total Successes: 19    Total Failures: 2
.
>  FFFFFFF   AA     III  L      U     U RRRRR   EEEEEEE
>  F        A  A     I   L      U     U R    R  E
>  F       A    A    I   L      U     U R     R E
>  F      A      A   I   L      U     U R     R E
>  FFFF   A      A   I   L      U     U RRRRRR  EEEE
>  F      AAAAAAAA   I   L      U     U R   R   E
>  F      A      A   I   L      U     U R    R  E
>  F      A      A   I   L       U   U  R     R E
>  F      A      A  III  LLLLLLL  UUU   R     R EEEEEEE
.

Though I haven’t enhanced this in a long time, I would welcome enhancements, feedback, suggestions, etc.  Obviously I have very little (to none) time to support this, but I’ll try to help where possible.

Download the zip file

Enjoy,

Stew