Debugging PL/SQL

It is important with any programming language either to be able to follow the code line by line or to be able to stop the code at certain points to check the contents of variables to ensure that the program is functioning as intended. Being able to visually see the code executing is an important ability when debugging or trying to find errors in the code. For example, if the expected result is not returned or the program never ends (a possible run-away loop), it would be nice to be able to step through the code, line by line, and check all the contents of all the variables.

Figure 3.22 uses Quest SQL Navigator to visualize the variables, including the returned values from the SQL statement with each iteration of the loop. Notice that a break point is set (with the mouse cursor on the line and pressing the Toggle Breakpoint button) on the last line of code inside the loop, then press the Run Debugger button and accept the defaults from the Calling Code Generation Wizard.

Each time you press the Run Debugger button, the loop is processed once. Figure 3.22 displays the variable v_total_sal (by pressing the X=? button with the mouse cursor on the variable). This variable is getting sal added to it in the FOR loop (the Run Debugger button was pressed three times). The Run button tells SQL Navigator to proceed to the next step or the next break point. This particular looping example shows the uses of an explicit cursor using the WHILE loop to process the result set of the multirow result set SQL query (the PL/SQL in Figure 3.21).