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.

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 – star t
  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, deter mine 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.

{mospagebreak title=utPLSQL: A Unit-Testing Framework}

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

{mospagebreak title=Debugging PL/SQL Programs}

When you test a program, you find errors in your code. When you debug a program, you uncover the cause of an error and fix it. These are two very different processes and should not be confused. Once a program is tested, and bugs are uncovered, it is certainly the responsibility of the developer to fix those bugs. And so the debugging begins!

Many programmers find that debugging is by far the hardest part of programming. This difficulty often arises from the following factors:

Lack of understanding of the problem being solved by the program

Most programmers like to code. They tend to not like reading and understanding specifications, and will sometimes forgo this step so that they can quickly get down to writing code. The chance of a program meeting its requirements under these conditions is slim at best.

Poor programming practice

Programs that are hard to read (lack of documentation, too much documentation, inconsistent use of whitespace, bad choices for identifier names, etc.), programs that are not properly modularized, and programs that try to be too clever present a much greater challenge to debug than programs that are well designed and structured.

The program simply contains too many errors

Without the proper analysis and coding skills, your code will have a much higher occurrence of bugs. When you compile a program and get back five screens of compile errors, do you just want to scream and hide? It is easy to be so overwhelmed by your errors that you don’t take the organized, step-by-step approach needed to fix those errors.

Limited debugging skills

There are many different approaches to uncovering the causes of your problems. Some approaches only make life more difficult for you. If you have not been trained in the best way to debug your code, you can waste many hours, raise your blood pressure, and upset your manager.

The following sections review the debugging methods that you will want to avoid at all costs, and then offer recommendations for more effective debugging strategies.

{mospagebreak title=The Wrong Way to Debug}

As I present the various ways you shouldn’t debug your programs, I expect that just about all of you will say to yourselves, “Well, that sure is obvious. Of course you shouldn’t do that. I never do that.”

And yet the very next time you sit down to do your work, you may very well follow some of these obviously horrible debugging practices.

If you happen to see little bits of yourself in the paragraphs that follow, I hope you will be inspired to mend your ways.

Disorganized debugging

When faced with a bug, you become a whirlwind of frenzied activity. Even though the presence of an error indicates that you did not fully analyze the problem and figure out how the program should solve it, you do not now take the time to understand the program. Instead you place MESSAGE statements (in Oracle Forms) or SRW.MESSAGE statements (in Oracle Reports) or DBMS_OUTPUT.PUT_LINE statements (in stored modules) all over your program in the hopes of extracting more clues.

You do not save a copy of the program before you start making changes because that would take too much time; you are under a lot of pressure right now, and you are certain that the answer will pop right out at you. You will just remove your debug statements later.

You spend lots of time looking at information that is mostly irrelevant. You question everything about your program, even though most of it uses constructs you’ve employed successfully for years.

You skip lunch but make time for coffee, lots of coffee, because it is free and you want to make sure your concentration is at the most intense level possible. Even though you have no idea what is causing the problem, you think that maybe if you try this one change, it might help. You make the change and take several minutes to compile, generate, and run through the test case, only to find that the change didn’t help. In fact, it seemed to cause another problem because you hadn’t thought through the impact of the change on your application.

So you back out of that change and try something else in hopes that it might work. But several minutes later, you again find that it doesn’t. A friend, noticing that your fingers are trembling, offers to help. But you don’t know where to start explaining the problem because you don’t really know what is wrong.

Furthermore, you are kind of embarrassed about what you’ve done so far (turned the program into a minefield of tracing statements) and realize you don’t have a clean version to show your friend. So you snap at the best programmer in your group and call your family to let them know you aren’t going to be home for dinner that night.

Why? Because you are determined to fix that bug!

Irrational debugging

You execute your report, and it comes up empty. You spent the last hour making changes both in the underlying data structures and in the code that queries and formats the data. You are certain, however, that your modifications could not have made the report disappear.

You call your internal support hotline to find out if there is a network problem, even though File Manager clearly shows access to network drives. You further probe as to whether the database has gone down, even though you just connected successfully. You spend another 10 minutes of the support analyst’s time running through a variety of scenarios before you hang up in frustration.

“They don’t know anything over there,” you fume. You realize that you will have to figure this one out all by yourself. So you dive into the code you just modified. You are determined to check every single line until you find the cause of your difficulty. Over the course of the next two hours, you talk aloud to yourself—a lot.

“Look at that! I called the stored procedure inside an IF statement. I never did that before. Maybe you can’t call stored programs that way.” So you remove the IF statement and instead use a GOTO statement to perform the branching to the stored procedure. But that doesn’t fix the problem.

“My code seems fine. But it calls this other routine that Joe wrote ages ago.” Joe has since moved on, making him a ripe candidate for the scapegoat. “It probably doesn’t work anymore; after all, we did upgrade to a new voicemail system.” So you decide to perform a standalone test of Joe’s routine, which hasn’t changed for two years and has no interface to voicemail. But his program seems to work fine—when it’s not run from your program.

Now you are starting to get desperate. “Maybe this report should only run on weekends. Hey, can I put a local module in an anonymous block? Maybe I can use only local modules in procedures and functions! I think maybe I heard about a bug in this tool. Time for a workaround …”

You get angry and begin to understand why your eight-year-old hits the computer monitor when he can’t beat the last level of Ultra Mystic Conqueror VII. And just as you are ready to go home and take it out on your dog, you realize that you are connected to the development database, which has almost no data at all. You switch to the test instance, run your report, and everything looks just fine.

Except, of course, for that GOTO and all the other workarounds you stuck in the report…

Please check back next week for the continuation of this article.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan