I don’t have room in this book to provide a complete explanation of how utPLSQL works. So I will instead try to impress you with how much it can do for you. Then you will be so excited that you will rush to the web site and take utPLSQL out for a drive all on your own.

Using utPLSQL with betwnstr

In the previous section, I started creating a grid of inputs and expected results for calls to betwnstr. I will now transform that grid into a delimited string that looks like this:

  DECLARE

        test_grid  VARCHAR2 (1000) := '
  betwnstr|1|start at 1|start at 1|abcdefgh;1;3|abc|eq|N
  betwnstr|1|start at 3|start at 3|abcdefgh;3;6|cde|eq|N
  betwnstr|1|null start|null start|abcdefgh;!null;2|null|isnull|Y
  betwnstr|1|null end||abcdefgh;!3;!null|null|isnull|Y
  betwnstr|1|null string||!null;1;2|NULL|isnull|Y
  betwnstr|1|big start small end||abcdefgh;10;5|null|isnull|Y
  betwnstr|1|end past string||abcdefgh;1;100|abcdefgh|eq|N';

I will then pass that string to a program in the utGen package, which will generate all of my test code for me:

  BEGIN
     utgen.testpkg_from_string ('betwnstr',
        test_grid,
        output_type_in=> utgen.c_file,
        dir_in=> 'TEMP'
     );
  END;

I then compile the ut_bewtnstr.pks and ut_betwnstr.pkb files that were generated:

  SQL> @ut_betwnstr.pks
  SQL> @ut_betwnstr.pkb

I am now ready to run my test, so I open a SQL*Plus session and issue this statement:

  SQL> exec utplsql.test ('betwnstr')

I am then presented with this information:    

>

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"
  .
  > Individual Test Case Results:
  >
  SUCCESS - EQ "start at 1" Expected "abc" and got "abc"
  FAILURE - EQ "start at 3" Expected "cde" and got "cdef"
  SUCCESS - ISNULL "null start" Expected "" and got ""
  SUCCESS - ISNULL "null end" Expected "" and got ""
  SUCCESS - ISNULL "null string" Expected "" and got ""
  SUCCESS - ISNULL "big start small end" Expected "" and got ""
  SUCCESS - EQ "end past string" Expected "abcdefgh" and got "abcdefgh"

Notice that utPLSQL shows me which of the test cases failed, what it expected, and what it received after running the test. So the first thing I do is go back to my test code (which in this case is simply a grid of test case inputs and outputs) and make sure I didn’t make any mistakes. I focus on this line:

  betwnstr|1|start at 3|start at 3|abcdefgh;3;6|cde|eq|N

It doesn’t take me long to realize that the “cde” or expected results is wrong. It should be “cdef.” So I change my test case information, regenerate my test code, run my test, and then am delighted to see this on my screen:

  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"

This is a very brief introduction to utPLSQL, but you can see that this framework automatically runs my test, and then tells me whether or not my test succeeded. It even reports on individual test cases.

utPLSQL was able to generate 100% of my test package for betwnstr, which is a bit of a special case in that it is a deterministic function (see Chapter 17 for more details on this characteristic). For most of the code you have written, you will be able to generate a starting point for your test package, but then complete it (and maintain it) manually.

utPLSQL doesn’t take all the pain out of building and running test code, but it pro vides a standardized process and a test harness from which you can run your tests and easily view results.

Where to find utPLSQL and Ounit

While there is a lot more to be said and demonstrated about utPLSQL, you should now have enough of an understanding of it to decide whether it might be of interest to you. To learn more about utPLSQL, the utAssert assertion routines, and the rest of this unit-testing framework, visit the project home for utPLSQL at:

  https://sourceforge.net/projects/utplsql/

You can also download utPLSQL along with a graphical interface to the test framework, named Ounit, at:

  http: www.ounit.com

Both products are free.

Onxo also offers a graphical interface to utPLSQL and adds test package generation capabilities as well. Check it out at:

  http://www.qnxo.com