< Previous Section: How to build a test package | Next Section: Examples >

A "Test Run" with utPLSQL

I will put utPLSQL to work in a small-scale development effort, to show you how it all hangs together. I've got a "hangnail" in my PL/SQL development work, called SUBSTR. This function bothers me and I want to take care of it. What's the problem? SUBSTR is great when you know the starting location of a string and number of characters you want. In many situations, though, I have the start and end locations and I need to figure out the number of characters I then want. Is it:

mystring := SUBSTR (full_string, 5, 17); -- start and end? Nah...

mystring := SUBSTR (full_string, 5, 12); -- end - start?

mystring := SUBSTR (full_string, 5, 13); -- end - start + 1?

mystring := SUBSTR (full_string, 5, 11); -- end - start 1 1?

Why should I have to remember stuff like this? I never do, and so I take out a scrap of paper, write down 'abcdefgh', put a mark over the "c" and another over the "g", count on my fingers and then remember that of course the formula is "end - start + 1".

All right, so I did that a dozen times, I am sick of it and determined to stop wasting my time in the future. I will write a function called "str.betwn" (the betwn function defined in the str package) that does the work and the remembering for me.

Instead of immediately coding the function, however, I will first write my unit tests with utPLSQL! Since my source package is named "str", I will create a test package named "ut_str". I am a lazy fellow, so I will take the lazy way out and generate the starting point for my package:

SQL> exec utgen.testpkg ('str', output_type_in => utplsql.c_file)

Note: for the above call to work, I must have already set my default directory for utPLSQL, which I do via a SQL*Plus login script that looks like this:

exec utplsql.setdir ('e:\utplsql\test')

SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED

Otherwise, I would need to specify the directory in my call to genpkg, as in:

SQL> exec utgen.testpkg ('str', output_type_in => utplsql.c_file, dir_in => 'e:\utplsql\test')

I then will find this package spec in the ut_str.pks file:

CREATE OR REPLACE PACKAGE ut_str
IS
   PROCEDURE ut_setup;
   PROCEDURE ut_teardown;
   
   -- For each program to test...
   PROCEDURE ut_betwn;
END ut_str;
/

And I don't really have to modify the specification at all. The body will, on the other hand, require some work, since I haven't yet figured out a way to automatically generate the test code itself. Here is the purely generated test package body found in the ut_str.pkb file:

CREATE OR REPLACE PACKAGE BODY ut_str
IS
   PROCEDURE ut_setup
   IS
   BEGIN
      NULL;
   END;
   
   PROCEDURE ut_teardown
   IS
   BEGIN
      NULL;
   END;
   
   -- For each program to test...
   PROCEDURE ut_betwn
   IS
   BEGIN
      utAssert.this (
         'Test of betwn',
         <boolean expression>,
      );
   END;
   
END ut_str;
/

The setup and teardown procedures are fine (I don't have any special setup and therefore teardown requirements), but the ut_betwn needs lots of work. It doesn't really test anything yet.

Before I start writing my test code, however, I will just sit back and think about what I want to test. Here are some inputs that I can think of:

String	Start	End	Expected Result
"this is a string"	3 (positive number)	7 (bigger positive number)	"is is"
"this is a string"	-3 (invalid negative number)	7 (bigger positive number)	"ing" (consistent with SUBSTR behavior)
"this is a string"	3 (positive number)	1 (smaller positive number)	NULL

We could easily come up with a whole lot more test cases - and if this was real life and not product documentation, I would not move forward until I had identified all interesting tests. So let's suppose I have done that and now I am ready to do some coding. Since I am testing a function, I will want to compare the result of the function call to my expected results. I will therefore change my assertion from the generic "assert this" procedure to the utAssert.eq program, and put the call to the function right into the assertion routine. Here, then, is my first crack at transforming my ut_betwn procedure:

PROCEDURE ut_betwn IS
BEGIN
   utAssert.eq (
      'Test of betwn',
      str.betwn ('this is a string', 3, 7),
      'is is'
   );
END;

Following the Extreme Programming philosophy ("code a little, test a lot"), I will test this test case before I add all the other test cases. I do this with a very simple call:

SQL> exec utplsql.test ('str')

>    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: "str"

Now, you could say: "Great it worked!" Or you could say: "I have no idea if it worked. Maybe it always says success." I go for the latter, so let's deliberately cause a failure:

PROCEDURE ut_betwn IS
BEGIN
   utAssert.eq (
      'Test of betwn',
      str.betwn ('this is a string', 3, 7),
      'this is a pipe'
   );
END;

Saving the file (but not bothering to recompile, since utPLSQL will do it for me automagically), I then run my test again:

SQL> exec utplsql.test ('str', recompile_in=>false)

>  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: "str"

BETWN: Typical Valid Usage; expected "is is", got "this is a pipe"

Now I have a higher degree of confidence that I am getting this right. Excellent! Now I will add the other test cases:

PROCEDURE ut_betwn IS
BEGIN
   utAssert.eq (
      'Typical Valid Usage',
      str.betwn ('this is a string', 3, 7),
      'is is'
   );
   
   utAssert.eq (
      'Test Negative Start',
      str.betwn ('this is a string', -3, 7),
      'ing'
   );
   
   utAssert.isNULL (
      'Start bigger than end',
      str.betwn ('this is a string', 3, 1)
   );
END;

I will deliberately cause each of these tests to fail, to give you a sense of the quality of feedback:

>  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: "str"
betwn: Typical Valid Usage; expected "is is", got "this is a pipe"
betwn: Test Negative Start; expected "ing", got "BRRRING"
betwn: IS NOT NULL: Start bigger than end

Faced with these results, I can zoom in on the code within str.betwn that is causing these incorrect results. I resist the temptation to fix the code for all my tests all at once. Instead, I make one change at a time, then run my test again. I do that over and over again until the failure for the single test case goes away. Then I move to the next one. Eventually, I get a green light and am highly confident of my program - if, of course, I really did come up with an exhaustive list of tests.

As I think of another test case, I add a call to utAssert to run that test.

As a bug is reported to me, I add a call to utAssert to reproduce that bug. Then I repair my code.

< Previous Section: How to build a test package | Next Section: Examples >