Home arrow Oracle arrow Testing PL/SQL Code

Testing PL/SQL Code

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

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:

String Start End Result
abcdefg 1 3 abc
abcdefg 3 6 cdef
N/A NULL NOT NULL NULL
N/A NOT NULL NULL NULL
NULL N/A N/A NULL
abcdefg Positive number Smaller than start NULL
abcdefg 1 Number larger than length of string abcdefg

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.



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