How to test for DBMS_OUTPUT with Quest Code Tester

What is the most common way of testing PL/SQL code? I think it is using DBMS_OUTPUT and manually verifying the results. Is it the best way of testing code? No, I don’t think so, but it is the most common way.

Steven Feuerstein created Code Tester, and if you are a frequent reader of this blog you may know that I really like this tool. However I have been struggeling with it for a bit the last couple of hours and that was while I was trying to figure out how to test DBMS_OUTPUT.

Set up

The stored procedure that I used for testing:

create or replace procedure put_test
is
begin
   dbms_output.put_line ('This is a test');
   dbms_output.put_line ('And so is this');
end put_test;

No arguments, no DML or anything fancy, just two lines of DBMS_OUTPUT.

Defining the Outcome

After starting Code Tester, create a new Test Definition in the Dashboard for the procedure we just created. If you are somewhat familiar with Code Tester, this should be easy. For those of you just starting out with this tool, scroll down to the step-by-step section.

1. Step 1 and 2 can be skipt, nothing to setup or teardown and no arguments
2. Step 3, the outcome

Choose "System Output" under "A. Data Changed by program" and use the pop up editor to specify the "Records Equal function". This was the thing that kept me busy for a good while. I was expecting that this function would accept two records or maybe a couple of associative arrays as its input. What threw me of was the pregenerated code. The "test for equality" code that is pregenerated as a suggestion has a signature like:

FUNCTION records_equal (
   record1_in    IN   dbms_output.chararr,
   record2_in    IN   dbms_output.chararr,
   nulls_eq_in   IN   BOOLEAN DEFAULT TRUE
)

But the test code that is actually generated to call this records_equal function does it like this:

...
RETURN NOT (records_equal
            (l_exp_collection (exp_index_in),
             l_fp_collection (fp_index_in),
             TRUE
            )
           );
...

Instead of two associative arrays, it calls the records_equal function with two scalars. Now that you know the implementation is easy. Use this function.

FUNCTION records_equal (
   record1_in    IN   VARCHAR2,
   record2_in    IN   VARCHAR2,
   nulls_eq_in   IN   BOOLEAN DEFAULT TRUE
)
   RETURN BOOLEAN
IS
BEGIN
   RETURN (   record1_in = record2_in
           OR (record1_in IS NULL AND record2_in IS NULL AND nulls_eq_in)
          );
END records_equal;

This records_equal function needs to be created on both sides of the operator (B. Operator).
On the right side of the operator, us the popup editor and fill it out like this screenshot.

And you’re all set. Save, Close and run and there is the happy smiley

Single Definition of Function

Instead of creating the same records_equal function in two places, it is easier to do it once in the Subprograms Section of the Builder.
This way you don’t have to create it twice in the Outcome section of the Builder. Make sure that you uncheck the checkbox in the first screenshot labelled "Use implementation of function below".

Step-by-Step

1. Right click in the Dashboard and choose "New Test Definition"
   
2. Choose the PUT_TEST procedure from the shown list
   
3. Create a new Test Case in the Builder
   
4. Name the test case "First Test" and move focus to the right hand side of the window. Choose the Subprograms tab. Add the Function Records_Equal (from this blog) to this tab
   
5. Add an outcome (step 2c on the screenshot) and choose System Output under "A. Data Changed by program". The operator ("B. Operator") and "C. Expected Results" are automatically filled in.
   
6. Click the button labelled […] under the "C. Expected Results" section
   
7. In the popup window fill in the Initialisation tab as shown
   
8. Push the "Save, Close and Run" button and wait for the smiley face to appear