Testing PL/SQL Code

Typical, Tawdry Testing Techniques

Say that I am writing a big application with lots of string manipulation. I’ve got a “hangnail” called SUBSTR; this function bothers me, and I need to take care of it.

What’s the problem? SUBSTR is great when you know the starting location of a string and the number of characters you want. In many situations, though, I have only the start and end locations, and then I have to compute the number of characters. But which formula is it?

  end - start
  end - start +1
  end - start - 1

I can never remember, so I write a program that will remember it for me—the betwnstr function:

  CREATE OR REPLACE FUNCTION betwnStr (
     string_in IN VARCHAR2,
     start_in IN INTEGER,
     end_in IN INTEGER
     )
     RETURN VARCHAR2
  IS
  BEGIN
     RETURN (
        SUBSTR (
           string_in,
           start_in,
           end_in - start_in + 1
           )
        );
  END;

The best way to test this program is to come up with a list of all the different test cases; here is a subset of the total, just to give you the idea:

From this grid, I can construct a simple test script like the following:

  SET SERVEROUTPUT ON FORMAT WRAPPED
  BEGIN
     DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefg', 1, 3));
     DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefg', 3, 6));
     DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefg', NULL, 2));
     DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefg', 3, NULL));
     DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefg', 5, 1));
     DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefg', 1, 100));
  END;

And when I run this code in SQL*Plus, I see the following results:

  SQL> @betwnstr.tst
  abc
  cdef

  abcdefg

And then I review the results and decide if the outcome matches my expectations. Of course, I have to be able to figure out just how many blank lines there were between “cdef” and “abcdefg”. Plus, if I am going to test this code thoroughly, I will probably have upwards of 30 test cases (what about negative start and end values?). It will take me at least several minutes to scan the results of my test. And this is a ridiculously simple piece of code. The thought of extending this technique to my “real” code is frightening.

If we are going to test effectively and thoroughly, we will need to take a different path. We need a way to define our tests so that they can easily be maintained over time. We need to be able to easily run our tests and then, most importantly, determine without lengthy analysis the outcome: success or failure.

Let’s take a look at how I would tackle the testing of betwnstr with a unit-testing framework such as utPLSQL.