Example

With utPLSQL, you build a test package for your standalone or packaged programs. You then ask utPLSQL to run the tests in your test package, and display the results. When you use utPLSQL, you don’t have to analyze the results and determine whether your tests succeeded or failed; the utility automatically figures that out for you.

Suppose, for example, that I have built a simple alternative to the SUBSTR function called betwnstr: it returns the substring found between the specified start and end locations in the string. Here it is:

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 betwnstr;
/

To test this function, I want to pass in a variety of inputs, as shown in this table:

From this table (which, of course, doesn’t yet cover all the variations needed for a comprehensive test), I build test cases for each entry in my test package’s unit test procedure:

CREATE OR REPLACE PACKAGE BODY ut_betwnstr
IS
   PROCEDURE ut_betwnstr IS
   BEGIN
      utassert.eq ('Typical valid usage',
         betwnstr (string_in => 'abcdefg', start_in => 3, end_in => 5),
         'cde'),

      utassert.isnull ('NULL start',
         betwnstr (string_in=> 'abcdefg',
            start_in    => NULL,
            end_in      => 5));

      utassert.isnull ('NULL end',
         betwnstr (string_in=> 'abcdefg',
            start_in    => 2,
            end_in      => NULL));

      utassert.isnull ('End smaller than start',
         betwnstr (string_in => 'abcdefg', start_in => 5, end_in => 2));

      utassert.eq ('End larger than string length',
         betwnstr (string_in=> 'abcdefg',
            start_in    => 3,
            end_in      => 200),
         'cdefg'),

   END ut_betwnstr;
END ut_betwnstr;
/

I call the utAssert procedures so that the results of my tests (my “assertions” that such and such is true) can be logged automatically with utPLSQL.

Then I can run the test and view the results. Here is a run that identifies no errors:

SQL> exec utplsql.test ('betwnstr')
.
>    SSSS   U     U   CCC     CCC   EEEEEEE   SSSS     SSSS
>   S    S  U     U  C   C   C   C  E        S    S   S    S
>  S        U     U C     C C     C E       S        S
>   S       U     U C       C       E        S        S
>    SSSS   U     U C       C       EEEE      SSSS     SSSS
>        S  U     U C       C       E             S        S
>         S U     U C     C C     C E              S        S
>   S    S   U   U   C   C   C   C  E        S    S   S    S
>    SSSS     UUU     CCC     CCC   EEEEEEE   SSSS     SSSS
.
 SUCCESS: "betwnstr"

And here is the output shown when problems arise:

SQL> exec utplsql.test ('betwnstr')
.
>  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
.
 FAILURE: "betwnstr"
.
UT_BETWNSTR: Typical valid usage; expected "cde", got "cd"
UT_BETWNSTR: IS NULL: NULL start
UT_BETWNSTR: IS NULL: End smaller than start

Benefits

You develop applications faster, with a higher degree of confidence and with fewer bugs.

It is much easier for other developers to maintain and enhance your code, because after they make a change, they can run the full suite of tests and confirm that the program still passes all tests.

Challenges

The only challenge to performing comprehensive unit testing is you! You know you have to test your code, and you have to test it repeatedly. So take the time to define your tests within a test package, and use a testing facility to run your tests for you.

Enlist the help of other developers in your organization to review your unit test cases and build others. Did you miss anything? Is your test accurate? It is often difficult for the person who wrote (or is about to write) the code to be objective about it. You’ll find more about this topic in [DEV-07: Get independent testers for functional sign-off.].

Resources