24. Debugging PL/SQL

Contents:
The Wrong Way to Debug
Debugging Tips and Strategies

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. In fact, you should probably have different people perform these steps. I have always found that the author of a program is the least effective tester of that program. Sure, you as the author should perform your unit tests and make a best effort to find bugs. But no project leader should ever rely on programmers for a complete test of their own programs. I have often found that a subtle, unconscious process takes place by programmers: they unknowingly follow steps through their code that avoid errors. They seem to know what it takes to make the program work and they execute those steps, rather than expose their own errors. (I stress that this is an unconscious process!)

Once a program is tested and bugs are uncovered, however, it is certainly the responsibility of the author 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 -- reading and understanding specifications are of less interest to them. They want to get down to it, doing what they do best and what they are most comfortable with: 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 usage of whitespace, bad choices for identifier names, etc.), not properly modularized, or those that try to be too clever present a much greater challenge to debug than those programs that are well designed and structured.
The program simply contains too many errors. Without the proper analysis and coding skills, you can end up with many more errors than is necessary. 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 section reviews the debugging methods that you will want to avoid at all costs, and then offers recommendations for more effective debugging strategies.

24.1 The Wrong Way to Debug

As I present the various ways you shouldn't debug your program, I expect that just about all of you will say to yourselves (some even without hypocrisy or the merest hint of shame), "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, unless you take a very disciplined approach and stick to your own (or your group's) rules, you will 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 are inspired to mend your ways.

24.1.1 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 from the program.

You do not save a copy of the program before you start to make 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 its 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 have caused 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!

24.1.2 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 the code that queries and formats the data. You are certain, however, that your modifications could not make 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 ten 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 or around 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." He has since moved on, making him a ripe candidate for the source of all problems in the world. "It probably doesn't work anymore; after all, we did upgrade to a new voice mail 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 voice mail. 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 understand better why your eight-year old hits the computer monitor when he can't beat the last level of Mystic Conqueror. 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.