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

The Wrong Way to Debug - 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.

  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



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.

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


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