Debugging PL/SQL Code

In this eighth part of a nine-part series on managing PL/SQL code, you will learn some effective debugging strategies. We’ll also begin talking about tuning PL/SQL programs. This artice 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.

Debugging Tips and Strategies

In this chapter, I do not pretend to offer a comprehensive primer on debugging. The following tips and techniques, however, should improve on your current set of error-fixing skills.

Use a source code debugger

The single most effective thing you can do to minimize the time spent debugging your code is to use a source code debugger. One is now available in just about every PL/SQL Integrated Development Environment (IDE). If you are using Quest’s Toad or SQL Navigator, Allround Automations’ PL/SQL Developer, or Oracle JDeveloper (or any other such GUI tool), you will be able to set visual breakpoints in your code with the click of a mouse, step through your code line by line, watch variables as they change their values, and so on.

The other tips in this section apply whether or not you are using a GUI-based debugger, but there is no doubt that if you are still debugging the old-fashioned way (inserting calls to DBMS_OUTPUT.PUT_LINE in dozens of places in your code), you are wasting a lot of your time. (Unfortunately, if your code is deployed at some customer site, debugging with a GUI tool is not always possible, in which case you usually have to resort to some sort of logging mechanism.)

Gather data

Gather as much data as possible about when, where, and how the error occurred. It is very unlikely that the first occurrence of an error will give you all the information you will want or need to figure out the source of that error. Upon noticing an error, the temptation is to show off one’s knowledge of the program by declaring, “Got it! I know what’s going on and exactly how to fix it.” This can be very gratifying when it turns out that you do have a handle on the problem, and that may be the case for simple bugs. Some problems can appear simple, however, and turn out to require extensive testing and analysis. Save yourself the embarrassment of pretending (or believing) that you know more than you actually do. Before rushing to change your code, take these steps:

Run the program again to see if the error is reproducible

This will be the first indication of the complexity of the problem. It is almost impossible to determine the cause of a problem if you are unable to get it to occur predictably. Once you work out the steps needed to get the error to occur, you will have gained much valuable information about its cause.

Narrow the test case needed to generate the error

I recently had to debug a problem in one of my Oracle Forms modules. A pop-up window would lose its data under certain circumstances. At first glance, the rule seemed to be: “For a new call, if you enter only one request, that request will be lost.” If I had stopped testing at that point, I would have had to analyze all code that initialized the call record and handled the INSERT logic. Instead, I tried additional variations of data entry and soon found that the data was lost only when I navigated to the pop-up window directly from a certain item. Now I had a very narrow test case to analyze, and it became very easy to uncover the error in logic.

Examine the circumstances under which the problem does not occur

“Failure to fail” can offer many insights into the reason an error does occur. It also helps you narrow down the sections of code and the conditions you have to analyze when you go back to the program.

The more information you gather about the problem at hand, the easier it will be to solve that problem. It is worth the extra time to assemble the evidence. So even when you are absolutely sure you are on to that bug, hold off and investigate a little further.

Remain logical at all times

Symbolic logic is the lifeblood of programmers. No matter which programming language you use, the underlying logical framework is a constant. PL/SQL has one particular syntax. The C language uses different keywords, and the IF statement looks a little different. The elegance of LISP demands a very different way of building programs. But underneath it all, symbolic logic provides the backbone on which you hang the statements that solve your problems.

The reliance on logical and rational thought in programming is one reason that it is so easy for a developer to learn a new programming language. As long as you can take the statement of a problem and develop a logical solution step by step, the particulars of a language are secondary.

With logic at the core of our being, it amazes me to see how often we programmers abandon this logic and pursue the most irrational path to solving a problem. We engage in wishful thinking and highly superstitious, irrational, or dubious thought processes. Even though we know better—much better—we find ourselves questioning code that conforms to documented functionality, that has worked in the past, and that surely works at that moment. This irrationality almost always involves shifting the blame from oneself to the “other”—the computer, the compiler, Joe, the word processor, whatever. Anything and anybody but our own pristine selves!

When you attempt to shift blame, you only put off solving your problem. Computers and compilers may not be intelligent, but they’re very fast and very consistent. All they can do is follow rules, and you write the rules in your program. So when you uncover a bug in your code, take responsibility for that error. Assume that you did something wrong—don’t blame the PL/SQL compiler, Oracle Forms, or the text editor.

If you do find yourself questioning a basic element or rule in the compiler that has always worked for you in the past, it is time to take a break. Better yet, it is time to get someone else to look at your code. It is amazing how another pair of eyes can focus your own analytical powers on the real causes of a problem.

Strive to be the Spock of Programming. Accept only what is logical. Reject that which has no explanation.

Analyze instead of trying

So you have a pile of data and all the clues you could ask for in profiling the symptoms of your problem. Now it is time to analyze that data. For many people, analysis takes the following form: “Hmm, this looks like it could be the answer. I’ll make this change, recompile, and try it to see if it works.”

What’s wrong with this approach? When you try a solution to see what will happen, what you are really saying is:

  1. You are not sure that the change really is a solution. If you were sure, you wouldn’t “try” it to see what would happen. You would make the change and then test that change.
  2. You have not fully analyzed the error to understand its causes. If you know why an error occurs, then you know if a particular change will fix that problem. If you are unsure about the source of the error, you will be tempted to simply try a change and examine the impact. This is, unfortunately, very faulty logic.
  3. Even if the change stops the error from occurring, you can’t be sure that your “solution” really solved anything. Because you aren’t sure why the problem occurred, the simple fact that the problem doesn’t reappear in your particular tests doesn’t mean that you fixed the bug. The most you can say is that your change stopped the bug from occurring under certain, perhaps even most, circumstances.

To truly solve a problem, you must completely analyze the cause of the problem. Once you understand why the problem occurs, you have found the root cause and can take the steps necessary to make the problem go away in all circumstances.

When you identify a potential solution, perform a walk-through of your code based on that change. Don’t execute your form. Examine your program, and mentally try out different scenarios to test your hypothesis. Once you are certain that your change actually does address the problem, you can then perform a test of that solution. You won’t be trying anything; you will be verifying a fix.

Analyze your bug fully before you test solutions. If you say to yourself, “Why don’t I try this?” in the hope that it will solve the problem, then you are wasting your time and debugging inefficiently.

Take breaks, and ask for help

We are often our own biggest obstacles when it comes to sorting out our problems, whether a program bug or a personal crisis. When you are stuck on the inside of a problem, it is hard to maintain an objective distance and take a fresh look.

When you are making absolutely no progress and feel that you have tried everything, try these two radical techniques:

  1. Take a break
  2. Ask for help

When I have struggled with a bug for any length of time without success, I not only become ineffective, I also tend to lose perspective. I pursue irrational and superstitious leads. I lose track of what I have already tested and what I have assumed to be right. I get too close to the problem to debug it effectively.

My frustration level usually correlates closely to the amount of time I have sat in my ergonomic chair and perched over my wrist-padded keyboard and stared at my low-radiation screen. Often the very simple act of stepping away from the workstation will clear my head and leave room for a solution to pop into place. Did you ever wake up the morning after a very difficult day at work to find the elusive answer sitting there at the end of your dream?

Make it a rule to get up and walk around at least once an hour when you are working on a problem—heck, even when you are writing your programs. Give your brain a chance to let its neural networks make the connections and develop new options for your programming. There is a whole big world out there. Even when your eyes are glued to the monitor and your source code, the world keeps turning. It never hurts to remind yourself of the bigger picture, even if that only amounts to taking note of the weather outside your air-conditioned cocoon.

Even more effective than taking a break is asking another person to look at your problem. There is something entirely magical about the dynamic of adding another pair of eyes to the situation. You might struggle with a problem for an hour or two, and finally, at the exact moment that you break down and explain the problem to a coworker, the solution will jump out at you. It could be a mismatch on names, a false assumption, or a misunderstanding of the IF statement logic. Whatever the case, chances are that you yourself will find it (even though you couldn’t for the last two hours) as soon as you ask someone else to find it for you.

And even if the error does not yield itself quite so easily, you still have lots to gain from the perspective of another person who (a) did not write the code and has no subconscious assumptions or biases about it, and (b) isn’t mad at the program.

Other benefits accrue from asking for help. You improve the self-esteem and self-confidence of other programmers by showing that you respect their opinions. If you are one of the best developers in the group, then your request for help demonstrates that you, too, sometimes make mistakes and need help from the team. This builds the sense (and the reality) of teamwork, which will improve the overall development and testing efforts on the project.

Change and test one area of code at a time

One of my biggest problems when I debug my code is that I am overconfident about my development and debugging skills, so I try to address too many problems at once. I make five or ten changes, rerun my test, and get very unreliable and minimally useful results. I find that my changes cause other problems (a common phenomenon until a program stabilizes, and a sure sign that lots more debugging and testing is needed), that some, but not all, of the original errors are gone, and that I have no idea which changes fixed which errors and which changes caused new errors.

In short, my debugging effort is a mess, and I have to back out of changes until I have a clearer picture of what is happening in my program.

Unless you are making very simple changes, you should fix one problem at a time and then test that fix. The amount of time it takes to compile, generate, and test may increase, but in the long run you will be much more productive.

Another aspect of incremental testing and debugging is performing unit tests on individual modules before you test a program that calls these various modules. If you test the programs separately and determine that they work, when you debug your application as a whole (in a system test), you do not have to worry about whether those modules return correct values or perform the correct actions. Instead, you can concentrate on the code that calls the modules. (See the earlier section “Testing PL/SQL Programs,” for more on unit testing.)

You will also find it helpful to come up with a system for keeping track of your troubleshooting efforts. Dan Clamage, a reviewer for this book, reports that he maintains a simple text file with running commentary of his efforts to reproduce the problem and what he has done to correct it. This file will usually include any SQL written to analyze the situation, setup data for test cases, a list of the modules examined, and any other items that may be of interest in the future. With this file in place, it’s much easier to return at any time (e.g., after you have had a good night’s sleep and are ready to try again) and follow your original line of reasoning.

{mospagebreak title=Tracing Execution of Your Code}

Earlier versions of Oracle offered some PL/SQL trace capabilities, but Oracle8i Database introduced an API that allows you to easily specify and control the tracing of the execution of PL/SQL procedures, functions, and exceptions. The DBMS_TRACE built-in package provides programs to start and stop PL/SQL tracing in a session. When tracing is turned on, the engine collects data as the program executes. The data is then written out to the Oracle Server trace file.

In addition to DBMS_TRACE, you can take advantage of the built-in function, DBMS_UTILITY.FORMAT_CALL_STACK, to obtain the execution call stack at any point within your application.

The PL/SQL trace facility provides a trace file that shows you the specific steps executed by your code. The DBMS_PROFILER package (described later in this chapter) offers a much more comprehensive analysis of your application, including timing information and counts of the number of times a specific line was executed.


This function returns the execution call stack (the sequence of program calls) down to the point at which you call the function. Here is an example of the formatting of this stack string:

  —– PL/SQL Call Stack —– –
  object   line    object
  handle   number  name

  88ce3f74   8  package
  88e49fc4   2  function STEVEN.COMPANY_TYPE
  88e49390   1  procedure
  88e2bd20   1  anonymous block

One of the best places to use this function is within an exception handler, as in:


Better yet, grab this information and write it to your log table, so that the support and debug teams can immediately see how you got to the point where the problem reared its ugly head.

There is, by the way, one big problem with the exception section above: if your call stack is deep, the formatted string will exceed 255 characters in length. Before Ora cle Database 10g Release 2, DBMS_OUTPUT.PUT_LINE would raise an exception in such cases. To avoid this problem, you might consider using Darko Egersdorfer’s callstack package, found in the callstack.pkg file on the book’s web site.

Installing DBMS_TRACE

This package may not have been installed automatically with the rest of the built-in packages. To determine whether DBMS_TRACE is present, connect to SYS (or another account with SYSDBA privileges) and execute this command:


If you see this error:

  PLS-00201: identifier ‘DBMS_TRACE.CLEAR_PLSQL_TRACE’ must be declared

then you must install the package. To do this, remain connected as SYS (or another account with SYSDBA privileges), and run the following files in the order specified:



DBMS_TRACE programs

The following programs are available in the DBMS_TRACE package:

Starts PL/SQL tracing in the current session

Stops the dumping of trace data for that session

Gets the major and minor version numbers of the
   DBMS_TRACE package

To trace execution of your PL/SQL code, you must first start the trace with a call to:


in your current session, where trace_level is one of the following values:

  • Constants that determine which elements of your PL/SQL program will be traced:

      DBMS_TRACE.trace_all_calls   constant INTEGER := 1;
                                   constant INTEGER := 2;
                                   constant INTEGER := 4;
                                   constant INTEGER := 8;
      DBMS_TRACE.trace_all_sql     constant INTEGER := 32;
                                   constant INTEGER := 64;
      DBMS_TRACE.trace_all_lines   constant INTEGER := 128;
                                   constant INTEGER := 256;

  • Constants that control the tracing process:

      DBMS_TRACE.trace_stop        constant INTEGER := 16384;
      DBMS_TRACE.trace_pause       constant INTEGER := 4096;
      DBMS_TRACE.trace_resume      constant INTEGER := 8192;
      DBMS_TRACE.trace_limit       constant INTEGER := 16;

By combining the DBMS_TRACE constants, you can enable tracing of multiple PL/SQL language features simultaneously. Note that the
con stants that control the tracing behavior (such as DBMS_TRACE.trace_ pause) should not be used in combination with the other constants (such as DBMS_TRACE.trace_enabled_calls).

To turn on tracing from all programs executed in your session, issue this call:


To turn on tracing for all exceptions raised during the session, issue this call:

  DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_all_exceptions);

You then run your code. When you are done, you stop the trace session by calling:


You can then examine the contents of the trace file. The names of these files are generated by Oracle; you will usually look at the modification dates to figure out which file to examine. The location of the trace files is discussed in the later section, “Format of collected data.”

Note that you cannot use PL/SQL tracing with the shared server (formerly known as the multithreaded server, or MTS).

{mospagebreak title=Controlling trace file contents}

The trace files produced by DBMS_TRACE can get really big. You can focus the output by enabling only specific programs for trace data collection. Note that you cannot use this approach with remote procedure calls.

To enable a specific program for tracing, you can alter the session to enable any programs that are created or replaced in the session. To take this approach, issue this command:


If you don’t want to alter your entire session, you can recompile a specific program unit in debug mode as follows (not applicable to anonymous blocks):


After you have enabled the programs in which you’re interested, the following call will initiate tracing just for those program units:

  DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_enabled_calls);

You can also restrict the trace information to only those exceptions raised within enabled programs with this call:

  DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_enabled_exceptions);

If you request tracing for all programs or exceptions and also request tracing only for enabled programs or exceptions, the request for “all” takes precedence.

Pausing and resuming the trace process

The SET_PLSQL_TRACE procedure can do more than just determine which information will be traced. You can also request that the tracing process be paused and resumed. The following statement, for example, requests that no information be gathered until tracing is resumed:


DBMS_TRACE will write a record to the trace file to show when tracing was paused and/or resumed.

Use the DBMS_TRACE.trace_limit constant to request that only the last 8,192 trace events of a run be preserved. This approach helps ensure that you can turn tracing on without overwhelming the database with trace activity. When the trace session ends, only the last 8,192 records are saved.

Format of collected data

If you request tracing only for enabled program units and the current program unit is not enabled, no trace data is written. If the current program unit is enabled, call tracing writes out the program unit type, name, and stack depth.

Exception tracing writes out the line number. Raising an exception records trace information on whether the exception is user-defined or predefined, and records the exception number in the case of predefined exceptions. If you raise a user-defined exception, you will always see an error code of 1.

Here is an example of the output from a trace of the showemps procedure:

  *** 1999.
  *** SESSION ID:(9.7) 1999.
  ———— PL/SQL TRACE INFORMATION —-   Levels set : 1

  Trace:  ANONYMOUS BLOCK: Stack depth = 1
  Trace:   PROCEDURE SCOTT.SHOWEMPS: Call to entry at line 5 Stack depth = 2
  Trace:    PACKAGE BODY SYS.DBMS_SQL: Call to entry at line 1 Stack depth = 3
  Trace:     PACKAGE BODY SYS.DBMS_SYS_SQL: Call to entry at line 1 Stack depth = 4
  Trace:     PACKAGE BODY SYS.DBMS_SYS_SQL: ICD vector index = 21 Stack depth = 4
  Trace:    PACKAGE PLVPRO.P: Call to entry at line 26 Stack depth = 3
  Trace:   PACKAGE PLVPRO.P: ICD vector index = 6 Stack depth = 3
  Trace:   PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 3
  Trace:   PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 3
  Trace:    PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 4

{mospagebreak title=Tuning PL/SQL Programs}

Tuning an Oracle application is a complex process: you need to tune the SQL in your code base, make sure the System Global Area is properly configured, optimize algorithms, and so on. Tuning individual PL/SQL programs is a bit less daunting, but still more than enough of a challenge. Before spending lots of time improving the performance of your PL/SQL code, you should first:

Tune access to code and data in the SGA

Before your code can be executed (and perhaps run too slowly), it must be loaded into the SGA of the Oracle instance. This process can benefit from a focused tuning effort, usually performed by a DBA. You will find more information about the SGA and other aspects of PL/SQL internals in Chapter 23.

Optimize your SQL

In virtually any application you write against the Oracle RDBMS, the vast majority of tuning will take place by optimizing the SQL statements executed against your data. The potential inefficiencies of a 16-way join dwarf the usual issues found in a procedural block of code. To put it another way, if you have a program that runs in 20 hours, and you need to reduce its elapsed time to 30 minutes, virtually your only hope will be to concentrate on the SQL within your code. There are many third-party tools available to both DBAs and developers that perform very sophisticated analyses of SQL within applications and recommend more efficient alternatives.

Once you are confident that the “context” in which your PL/SQL code is run is not obviously inefficient, you should turn your attention to the code base. I suggest the following steps:

Write your application with best practices and standards in mind

While you shouldn’t take clearly inefficient approaches to meeting requirements, you also shouldn’t obsess about the performance implications of every line in your code. Remember that most of the code you write will never be a bottleneck in your application’s performance, so you don’t have to optimize it. Instead, get the application done and then…

Analyze your application’s execution profile

Does it run quickly enough? If it does, great: you don’t need to do any tuning (at the moment). If it’s too slow, identify which specific elements of the application are causing the problem and then focus directly on those programs (or parts of programs). Once identified, you can then…

Tune your algorithms

As a procedural language, PL/SQL is often used to implement complex formulas and algorithms. You can use conditional statements, loops, perhaps even GOTOs and (I hope) reusable modules to get the job done. These algorithms can be written in many different ways, some of which perform very badly. How do you tune poorly written algorithms? This is a tough question with no easy answers. Tuning algorithms is much more complex than tuning SQL (which is “structured” and therefore lends itself more easily to automated analysis).

Take advantage of any PL/SQL-specific performance features

Over the years, Oracle has added statements and optimizations that can make a substantial difference to the execution of your code. Consider using constructs ranging from the RETURNING clause to FORALL. Make sure you aren’t living in the past and paying the price in application inefficiencies.

It’s outside the scope of this book to offer substantial advice on SQL tuning and database/SGA configuration. Even a comprehensive discourse on PL/SQL tuning alone would require multiple chapters. Further, developers often find that many tuning tips have limited or no impact on their particular environments. In the remainder of this chapter, I will present some ideas on how to analyze the performance of your code and then offer a limited amount of tuning advice that will apply to the broadest range of applications.

Please check back next week for the conclusion to this article.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye