4.14.1 Debugging a report

To debug a report:

Run the report in debug mode (described below) to check for logical errors in the report, and displays these as warnings at runtime, before displaying the report output. Running a report in debug mode is not the same as debugging a report using the PL/SQL Interpreter.

After narrowing the failing code to a specific region, use the PL/SQL Interpreter to implement and test possible code fixes.

Usage notes

When debugging a JSP-based Web report in Oracle JDeveloper, you must disable any breakpoints that are not of Source type. If these breakpoints are not disabled, a deadlock is detected. You can disable breakpoints that are not of Source type in the Breakpoints window.

4.14.2 Running a report in debug mode

To compile and run a report in debug mode:

Choose Edit > Preferences.

In the Preferences dialog box, on the Runtime Settings page, select Run Debug. Click OK.

Click the Run Paper Layout button in the toolbar to run the report.

To compile and run a report in debug mode from the command line:

On the rwbuilder or rwrun command line, specify RUNDEBUG=YES. For information about RUNDEBUG, see the Reference > Command Line section of the Oracle Reports online Help.

Usage notes

Running a report in debug mode specifies that you want extra runtime checking for logical errors in the report. It checks for things that are not errors but might result in undesirable output, and displays these as warnings at runtime, before displaying the report output. Running a report in debug mode is not the same as debugging a report using the PL/SQL Interpreter.

4.14.3 Setting a breakpoint

To set a breakpoint in the execution of your report:

If the PL/SQL Interpreter is not already displayed, choose Tools > PL/SQL Interpreter.

In the Object Navigator, single-click a compiled program unit node to display the program unit in the Interpreter Source pane.

Note:

Uncompiled program units are indicated by an asterisk (*) after their name.

Double-click an executable statement (a PL/SQL construct used for conditional, iterating, and sequential control, and for error handling. A semi-colon (;) must terminate every PL/SQL statement) where you wish to create the break point.

Tip:

You cannot place a breakpoint on a BEGIN, END, or NULL, statement, or on a comment.

The breakpoint is inserted and is indicated by B00n, where n is the number of the breakpoint. When you run the program unit, execution is suspended at the line just prior to the breakpoint.

4.14.4 Setting a debug trigger

To set a debug trigger:

If the PL/SQL Interpreter is not already displayed, choose Tools > PL/SQL Interpreter.

In the Object Navigator, single-click a compiled program unit node to display the program unit in the Interpreter source pane.

Note:

Uncompiled program units are indicated by an asterisk (*) after their name.

In the Source pane, select the line where you want to create the debug trigger, then choose Program > Debugging Triggers (or right-click in the Source pane and choose Trigger).

Tip:

You cannot place a breakpoint on a BEGIN, END, or NULL statement, or on a comment.

In the PL/SQL Trigger dialog box, define the trigger:

If you want the trigger to fire at different location than the current program unit, select a location from the Location list.

Type the debug trigger in the Trigger Body text box.

For example, to create a debug trigger that interrupts program execution if the local NUMBER variable my_sal exceeds 5000, enter the following as the trigger body:

IF Debug.Getn('my_sal') > 5000 THEN
raise Debug.Suspend;
END IF;

Note:

To create a debug trigger that contains multiple lines of text, include a BEGIN and an END statement around the code.

You must raise the DEBUG.SUSPEND exception from the DEBUG package if you want the PL/SQL Interpreter to appear when this line is executed. Otherwise, Oracle Reports Builder executes the code silently and the PL/SQL Interpreter does not appear.

Click OK to create a debug trigger for the selected line.

Tip:

You can also create a debug trigger by entering commands in the PL/SQL Interpreter pane.

4.14.5 Browsing debug actions

To browse debug actions:

In the Object Navigator, expand the Debug Actions node. The Object Navigator displays a list of the debug actions associated with the current report.

or

In the PL/SQL Interpreter, use the DESCRIBE command to display information about a specific debug action.

For example, entering DESCRIBE BREAK 1 in the Interpreter pane reveals the following about Breakpoint 1:

4.14.6 Editing a debug action

In the Object Navigator, expand the Debug Actions node, then double-click the debug action icon to display the appropriate dialog box.

Edit the content or properties of the debug action in the dialog box.

Click OK.

4.14.7 Disabling and enabling debug actions

To disable/enable a debug action:

In the Object Navigator, under the Debug Actions node, right-click the desired debug action and choose Enable or Disable.

or

In the PL/SQL Interpreter, use the ENABLE or DISABLE command to enable or disable the specific debug action.

For example, entering .DISABLE ACTION 1 in the Interpreter pane disables the first debug action and displays the following status:

Disabling debug action 1...

4.14.8 Deleting a debug action

To delete a debug action:

In the Object Navigator, expand the Debug Actions node, then click the action you want to delete.

Click the Delete button in the toolbar.

4.14.9 Running a program unit in the PL/SQL Interpreter

To run a program unit in the PL/SQL Interpreter:

If the PL/SQL Interpreter is not already displayed, choose Tools > PL/SQL Interpreter to display it.

At the Interpreter's PL/SQL> prompt, type the name of the program unit followed by a terminating semi-colon (;). If the program unit requires any arguments, be sure to supply them in parentheses. For example: getdata(SCOTT);

Press the Enter or Return key to produce one of the following reactions:

Any output generated by the program unit is displayed at the command line, and the PL/SQL> prompt returns to indicate successful execution.

The secondary prompt appears (+>) indicating you have not finished entering an executable statement. If you forgot the terminating semicolon, enter it now and press Enter or Return. Otherwise, right-click and choose New Prompt.

Runtime errors are displayed at the command line, then the PL/SQL> prompt appears. You need to edit or debug your program unit.

If you have set a breakpoint or debug trigger in the program unit, execution is suspended and a new prompt is displayed as: (debug n)PL/SQL>.

Usage notes

Running a program unit as described above only works for procedures (or packaged procedures), not for functions (since there's no variable for a return value to be returned to).

4.14.10 Inserting a Navigator pane in the PL/SQL Interpreter

To insert an Object Navigator pane in the PL/SQL Interpreter:

If the Interpreter is not already displayed, choose Program > PL/SQL Interpreter.

In the PL/SQL Interpreter, choose View > Navigator Pane to insert the Object Navigator pane in the middle of the PL/SQL Interpreter.

Notice that the button bar is updated with new Object Navigator buttons.

Optionally, use the split bars to resize the proportions of the three panes.

4.14.11 Controlling program unit execution

Once you have inspected and modified the program state, you can resume or terminate execution using the following features:

Table 4-10 Program unit execution

Execution Feature

Description

STEP

You can use the STEP command to temporarily resume execution of an interrupted program. Control returns to the PL/SQL Interpreter after the specified set of statements have been executed. STEP Into or Over enables you to:

execute the next statement (optionally descending into subprogram calls)

resume execution until the current subprogram has returned

continue execution until the specified source location is reached

GO

Use the GO command to resume program execution indefinitely--that is, until either the currently executing thread of execution terminates or it is interrupted again due to a debug action.

RESET

Use the RESET command to return control to an outer debug level without continuing execution in the current debug level. Thus, RESET effectively aborts execution at the current (and possibly higher) debug levels.

You can explicitly reset execution to any previous debug level, or you can simply reset to top level, which is the default.

Execute these commands from either the PL/SQL Interpreter toolbar or by typing the command in the PL/SQL Interpreter pane.

4.14.12 Stepping through the code

To step through the code:

Before proceeding, you must already have set a debug action such as a breakpoint, and run your program unit at the PL/SQL Interpreter PL/SQL> prompt to suspend execution.

To step to the next line of the suspended program unit:

Click the Step Into button in the PL/SQL Interpreter toolbar to execute the next line of executable code in the current program unit.

If the next executable line is a call to a nested subprogram (a program unit that is called from within another program unit), Step Into halts execution at the first line of the nested subprogram.

To step over a nested subprogram call in the suspended program unit:

Click the Step Over button in the PL/SQL Interpreter toolbar.

Step Over executes any calls to nested subprograms and then halts execution at the next executable line of the current program unit.

To step out of a nested subprogram and return to the outer program unit:

Click the Step Out button in the PL/SQL Interpreter toolbar.

If you previously used Step In to descend into a nested subprogram, Step Out completes execution of the nested subprogram and returns to the next line of the original program unit.

To resume program unit execution:

Click the Go button in the PL/SQL Interpreter toolbar.

Execution of the program unit continues until the program unit execution has finished, or until interrupted again by another debug action.

Note:

If your debug action is located in a PL/SQL LOOP, using Go will cycle through the loop.

To exit suspended execution at the current debug level:

Click the Reset button in the PL/SQL Interpreter toolbar.

Control is returned to the Interpreter, or to an outer debug level (if any exist).

4.14.13 Modifying code at runtime

To modify your code at runtime:

In the Object Navigator, double-click the desired program unit, menu item command, or trigger to display the PL/SQL Editor.

In the PL/SQL Editor, make the desired modifications.

Click Compile then Close to dismiss the PL/SQL Editor.

In the PL/SQL Interpreter toolbar, choose Go or Step Into, Over, or Out to resume program execution.

4.14.20 Tracing using the SQL TRACE function

The TRACE function provides you with the exact statements that are being parsed. Once you have them, you can time them in SQL*Plus, and multiply these times with the expected number of rows to retrieve from the database. (Always compare apples to apples; that is, send output to a file--not to the screen. Do not change anything in the SQL statements when moving it to SQL*Plus; even the slightest change in the WHERE clause can make a big difference in the performance.)

There are two ways to trace your reports:

user level

system level

We recommend the user level because you can more easily find the information you need. For more information on SQL TRACE, see the Oracle Server SQL Language Reference manual.

4.14.20.1 Performing a user-level trace

Open the report for which you want the performance data.

Create a report-level formula column named SQL-TRACE that has the following formula:

Run the report. A new file, some_number.trc, will be created in either ORACLE_HOME/rdbms/log, or the destination indicated by the init.ora parameter USER_DUMP_DEST. (The date stamp on the file can help you determine which .trc file is yours.)

Use the TKPROF command to format the trace output file.

If you issue more than one trace during the rwbuilder session, the trace outputs are concatenated into one file.

4.14.20.2 Performing a system-level trace

Insert these statements into your init.ora file: SQL_TRACE=TRUE and TIMED_STATISTICS=TRUE.

Shut down, then restart your database.

Note:

Every interaction with the database will be traced, and the ORACLE_HOME/rdbms/log is likely to grow very large.

Scripting on this page enhances content navigation, but does not change the content in any way.