Overview

PL/SQL Procedures more times than not implement complex business rules and logic.  DBMS output statements and the SQL Developer debugger are tools that can assist when initially testing the procedure but when a change is made to a procedure it must be thoroughly regression-tested to ensure the new or change feature hasn’t broken something else.  While SQL Developer provides its own unit testing framework it’s more than a little tedious to setup and it requires the unit tests be run in SQL Developer.  Alternatively, the approach described in this topic proposes the use of a SQL script to create the test data and to run the test and record the results in a custom unit testing table.

The setup described here is entirely based on Oracle SQL and can be run outside of SQL Developer

Unit Test Table

The approach here requires creating one custom table to store the results of the unit tests.  Unit tests are organized in a folder of tests testing the same PL/SQL package.  Each of the unit testing scripts in the folder will record their tests results (sample DDL provided below) according to this group name (TEST_GROUP), whatever you want to call it.  In addition, there is a TEST_RESULT column that records PASSED or FAILED associated with the unit test name and description.

DROP TABLE "UNIT_TESTING_TABLE" CASCADE CONSTRAINTS;   
CREATE TABLE "UNIT_TESTING_TABLE" (
    "TEST_GROUP"        NVARCHAR2(30),
    "TEST_NAME"         NVARCHAR2(30),
    "TEST_DESCRIPTION"  NVARCHAR2(1000),
    "TEST_RESULT"       NVARCHAR2(20),
    "TEST_DETAILS"      NVARCHAR2(1000),
    "CREATED_DATE"      TIMESTAMP (6) DEFAULT ON NULL systimestamp
   ) SEGMENT CREATION IMMEDIATE
LOGGING;
 
ALTER TABLE "UNIT_TESTING_TABLE" MODIFY ("TEST_RESULT" NOT NULL ENABLE);
ALTER TABLE "UNIT_TESTING_TABLE" MODIFY ("TEST_NAME" NOT NULL ENABLE);
ALTER TABLE "UNIT_TESTING_TABLE" MODIFY ("TEST_GROUP" NOT NULL ENABLE);

Running the Unit Tests

A SQL script to run the unit tests will be written in the same unit testing directory (e.g. Run-Initiator-UnitTests.sql) which first will clean up the unit testing table (line 1), invoke each of the unit test scripts (lines 3-6) in the directory and then display the PASSED or FAILED results (lines 8-16).

Writing a Unit Test

Each of the unit tests will have four sections:

• Describe unit test
• DELETE and INSERT test data
• Initialize and call the PL/SQL Procedure
• Assert test results and record the results

A sample script will be provided below to document each of these four sections.  The first two are easy enough and are shown below.

The third section is easy to create, simply open the Test or Debug dialog of the PL/SQL Procedure. An EXCEPTION variable is declared on line 22 below.  Lines 23-25 were added to this to set various variables needed to record the results in the unit testing table.

 Lines 30-37 invoke the PL/SQL Procedure under test.

Immediately after calling the PL/SQL Procedure add IF conditions to test the outcome.  If any of these “assertions” are true then invoke the EXCEPTION.   If the EXCEPTION is not raised then insert the PASSED status.  Note the implementation of the failed_the_test EXCEPTION on lines 55-57 that will insert the FAILED status.

 Lines 58-60 provide an OTHERS exception to catch unexpected exceptions from the procedure under test.  If the procedure under test throws an exception that is expected, then define an EXCEPTION for it in the exception block and set the test status to PASSED.  

Best Practices

• The unit tests are run in a local or isolated Oracle database instance.  The reason for this is that each of the tests delete and create their own test data, which may wreak havoc in your DEV and TEST environments.
• The unit testing table contains a description and it should describe what the tests is about so anyone looking at either the results in the table or in the unit test will understand the breadth of what is being tested. 
• The unit test file name itself should match (tersely) so anyone looking at the unit tests folder can distinguish one from another. 
• The assertions can query any table in the database under test and need not reflect only what is returned from the procedure.
• Execute the unit test scripts in the file name order seen in the folder which will ensure you aren’t missing any tests.

Summary

Having the unit tests written in a pure Oracle syntax and execution mode is intuitive, learned easily and provides a robust means to regression test PL/SQL procedures.  The unit testing table will record each of the unit test group results so test failures can be easily located.