Basic Debugging PLSQL Code


Basic Debugging PL/SQL Code

The TOAD debugger is easily accessed by using the nine buttons on the right side of the Procedure Editor toolbar (middle toolbar). See Figure 4.48. These same functions are also accessible from the Debug menu (see Figure 4.49), and all of the functions have a shortcut keystroke as well.

Figure 4.48. The Debug toolbar.

Figure 4.49. The Debug drop-down menu.

The following keystrokes access many of the Debug features.

F11 = Run

Ctrl+F9 = Set Parameters

F8 = Step Over

F7 = Trace Into

Shift+F8 = Trace Out

F12 = Run to Cursor

Ctrl+F5 = Add Watch

The TOAD debugger relies heavily upon certain functions. These functions, described in the following list, are used throughout this section.

  • Set Parameters : Allows you to set any input variables .

  • Run : Executes the procedure/function.

  • Run to Cursor : Executes the procedure/function until the line containing the mouse cursor is reached.

  • Step Over : Executes one line of code at a time but does not go into any called procedures or functions.

  • Trace Into : Executes one line of code at a time and does go into any called procedure or function, executing its code one line at a time as well.

  • Trace Out : Returns execution to the calling routine, stopping on the next line of code after the call statement.

  • Halt Execution : Stops debugging or stepping through the lines of code.

  • Set Breakpoint : Stops the debug process at the line with the breakpoint.

  • Add Watch at Cursor : Watches allow for the contents of specific variables to be monitored during the debug process. This key allows you to add additional variables to be monitored .

  • Evaluate/Modify : Allows for "watched" variables to be visualized and changed on the fly.

  • Breakpoints : A breakpoint stops execution at the line with the breakpoint. This key allows you to add, change, or delete breakpoints.

  • Call Stack : Displays the call stack, or an ordered list of procedures/functions that were called to get to this particular procedure/function.

  • Watches : Displays the variables currently being monitored. This window also allows you to add, change, or delete watched variables.

  • DBMS Output : Displays any DBMS_OUTPUT generated by the procedure/function.

Please note that DBMS_OUTPUT is not displayed until the procedure/function completes execution or the Halt button is used.


Let's walk through a simple debugging session using TEMPERATURE.PKG, which contains a procedure and its related functions.

These procedures and functions are available on the Web site for this book.


The debugging process relies heavily upon the Oracle Probe API. With this in mind, the TOAD debugger cannot see any of the code variables until the code is executing.

The two main buttons to go through the code are the Trace Into and Step Over buttons/functions. Trace Into stops on each line of code. Step Over does the same thing except it will not follow the lines of code into a called procedure or function; the code will be executed and the debugger will simply stop on the next line of code in the current procedure.

The author speaks of named buttons, but often in TOAD there is a shortcut and/or keystroke available for the same function. For simplicity, the author will speak of the named button.


You can set "watches" to see and edit the content of variables. These watches allow you to see the changes in the contents of the variables as the code is being debugged , in the tab at the bottom of the screen (see Figure 4.52). You can also hover over a variable with the mouse and it will display the contents as well.

Figure 4.52. Watching a variable.

To begin a debugging session, load TEMPERATURE.PKG and click the Compile Dependencies with Debug button (or simply click the Compile button after clicking the Toggle Compiling with Debug button, in the middle of top toolbar). This puts the necessary debug information into the procedures/functions. If you do not use this function, you might get the message No Debug Information Available .

Click the Trace Into button. The parameter screen will pop up; see Figure 4.50. Ignore the Celsius_to_Fahrenheit and the Fahrenheit_to_Celsius functions for now. Click on the Temperature_Conversion procedure and set IN_TEMP to 10 and IN_TYPE to C. Click on OK.

Figure 4.50. TOAD runtime parameter input.

Now set a watch on Converted_Temp. The debugger should have stopped on this first line. You can do this in one of three ways:

  • Use the Watches tab line and the '+' button.

  • Right-click on the variable name , select the Add Watch button from the toolbar, and then select Debug, Add Watch at Cursor (see Figure 4.51).

    Figure 4.51. Setting a watch.

  • Press Ctrl+F5.

Notice in the Watches tab (see Figure 4.52) that it added the variable and that it is NULL. TOAD has not executed this first statement yet, so the variable is not set to 0 yet. Press F7 or click the Trace Into button and watch it change.

The Halt button will stop execution of the code at any time. You can walk through the code, see the content of variables anywhere they appear, and change the contents of the variables on the fly by selecting the variable in the Watch tab and clicking the Evaluate/Modify button (see Figure 4.53). This enables you to change the contents of a variable right before a critical check or procedure for testing purposes.

Figure 4.53. Changing variable content.

Rather than stepping through code one line at a time, you might want to run a bunch of code and either stop at a designated point (a breakpoint) or where the cursor is in the code. You can use the Run to Cursor button or press F12 to execute the code until it gets to the line with the mouse cursor. You can set a breakpoint in a number of ways (by clicking just to the left of the line where you want the breakpoint, as shown in Figure 4.54, or by using the Break Points tab with the '+', or by pressing F5). This allows you to execute the code by clicking the Run button or pressing F11, and the debugger will stop when it gets to this line. Later in this chapter, you will learn how to set conditional breakpoints, or the debugger will stop on a line only when certain conditions exist (such as looped ten or more times, and so on). Stopping the code at convenient times is how you evaluate variable content at critical points in the code.

Figure 4.54. Setting breakpoints.

Debugging triggers are different from debugging procedures. The INSERT INTO , UPDATE .. SET , and DELETE trigger code is not available to the debugger until the values have been entered.


When debugging something like FOR LOOP_COUNT IN 1..500 LOOP ... , set a watch on the LOOP_COUNT variable and use Evaluate/Modify to change 500 to a much smaller number so that you don't have to loop 500 times to see it exit the routine.




TOAD Handbook
TOAD Handbook (2nd Edition)
ISBN: 0321649109
EAN: 2147483647
Year: 2003
Pages: 171

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net