4.14 Debug a Report

This section provides procedures for the following tasks that you may perform as you debug a report:

For conceptual information that supports these tasks, see Section 2.10, "Debugging Tools".

4.14.1 Debugging a report

To debug a report:

  1. 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.

  2. After identifying a problem area, choose Tools > PL/SQL Interpreter to display the PL/SQL Interpreter, and create the desired debug actions (see Section 4.14.3, "Setting a breakpoint" and Section 4.14.4, "Setting a debug trigger") to isolate the failing code to a specific region of number of statement.

  3. Close the PL/SQL Interpreter to run your report.

  4. Browse your debug actions and evaluate application information.

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

  1. Choose Edit > Preferences.

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

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

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

  2. 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.

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

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

  2. 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.

  3. 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.

  4. 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.

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

    Breakpoint: 1
    Program Unit: Procedure Body <programunit_name>
    Line: 3
    Enabled: YES
    

4.14.6 Editing a debug action

To edit a debug action:

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

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

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

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

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

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

  2. 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);

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

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

  2. 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.

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

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

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

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

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

See also

Section 2.10.9, "About modifying code at runtime"

4.14.14 Displaying the current scope location

To display the current scope location:

  • In the Object Navigator, expand the Stack node, then expand the desired frame in the stack to reveal information about local variables and parameters.

    or

  • In the PL/SQL Interpreter, display the Source pane to view the current scope location.

See also

Section 2.10.7, "About the current scope location"

4.14.15 Examining or changing local variables

You must currently have a suspended program unit in the PL/SQL Interpreter to examine or change local variables values.

To examine local variable values:

  1. In the Object Navigator, expand the Stack node to show the call stack frames.

  2. Under the Stack node, double-click the node for the program unit whose variables you wish to examine or modify.

    Each local variable is displayed with its current value.

To edit local variable values:

  1. In the Object Navigator, expand the Stack node to show the call stack frames.

  2. Under the Stack node, double-click the node for the program unit whose variables you wish to examine or modify.

    Each local variable is displayed with its current value.

  3. Click the variable value in the Object Navigator.

  4. Click the variable value again to enter edit mode.

  5. Change the value of the variable.

  6. Click a blank area in the Object Navigator to exit edit mode and accept the changed value.

  7. Resume program unit execution in the PL/SQL Interpreter to test the effect of the new value.

4.14.16 Modifying application variables

To modify a variable:

  1. In the Object Navigator, expand the Global Variables node or the Stack node for local variables.

  2. Select and expand the desired entry. The Object Navigator displays any variables associated with the entry.

  3. Click the existing value after the "=" then edit the value by entering the desired value.

4.14.17 Viewing subprogram references

To view subprogram references:

  1. In the Object Navigator, select and expand the desired subprogram.

  2. Select and expand either the References or Referenced By node.

    The Object Navigator displays any subprogram references.

    Note:

    Referenced By shows the program units that call the current program unit. References shows the program units that are called by the current program unit.

4.14.18 Tracing report execution

To set tracing options, do one of the following:

  • Choose Program > Tracing and fill out the Runtime Trace Settings dialog box as desired.

  • In the ReportsTools logging configuration file (logging.xml), set the trace level in the level attribute of the logger element (oracle.reports.runtime).

For more information, including tracing for rwserver and rwservlet, see the chapters "Configuring OracleAS Reports Services" and "Tuning Oracle Reports" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services manual.

4.14.19 Tracing report distribution

To trace report distribution:

  1. Choose Program > Tracing.

  2. In the Runtime Trace Settings dialog box, specify a name for the trace file in the Trace File field.

  3. Select the Distribution check box.

  4. Click OK.

  5. Run the report.

  6. Use a text editor to open and view the trace file. If the trace file is empty, the distribution was successful. Otherwise, the trace file identifies the distribution error.

See also

Section 2.8.3, "About report distribution"

Section 4.7.12, "Distributing a report to multiple destinations"

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

  1. Open the report for which you want the performance data.

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

    SRW.DO_SQL ('ALTER SESSION SET SQL_TRACE=TRUE');
    return(1); --Formulas must return a value.
    

    Note:

    You could also call SQL TRACE from the Before Form trigger.

  3. 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.)

  4. 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

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

  2. 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.