Home arrow Oracle arrow Page 2 - Testing PL/SQL Code

utPLSQL: A Unit-Testing Framework - Oracle

In this seventh part of a nine-part series on managing PL/SQL code, you'll learn how to test your code, and some good techniques to NOT use for debugging it. This article is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O'Reilly; ISBN: 0596009771). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

TABLE OF CONTENTS:
  1. Testing PL/SQL Code
  2. utPLSQL: A Unit-Testing Framework
  3. Debugging PL/SQL Programs
  4. The Wrong Way to Debug
By: O'Reilly Media
Rating: starstarstarstarstar / 11
December 06, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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 provides 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



 
 
>>> More Oracle Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: