Home > SQL Developer Concepts and ... > Running and Debugging Funct...
You can use SQL Developer to run and debug PL/SQL subprograms (functions and procedures).
To run a subprogram, click its name in the Connections navigator; then either right-click and select Run, or click the Edit icon and then click the Run icon above its source listing.
To debug a subprogram, click its name in the Connections navigator. If the procedure in its current form has not already been compiled for debug, right-click and select Compile for Debug. Then click the Edit icon and click the Debug icon above its source listing.
In both cases, a code editing window is displayed. The following figure shows the code editing window being used to debug a procedure named LIST_A_RATING2, which is used for tutorial purposes in Debug a PL/SQL Procedure.
In the code editing window, under the tab with the name of the subprogram, is a toolbar, and beneath it is the text of the subprogram, which you can edit. You can set and unset breakpoints for debugging by clicking to the left of the thin vertical line beside each statement with which you want to associate a breakpoint. (When a breakpoint is set, a red circle is displayed.)
The toolbar under the tab for the subprogram name includes the icons shown in the following figure.
Freeze Content (the pin) keeps that subprogram's tab and information in the window when you click another object in the Connections navigator; a separate tab and display are created for that other object. If you click the pin again, the object's display is available for reuse.
Run starts normal execution of the subprogram, and displays the results in the Running - Log tab.
Debug starts execution of the subprogram in debug mode, and displays the Debugging - Log tab, which includes the debugging toolbar for controlling the execution.
Compile for Debug performs a PL/SQL compilation of the subprogram so that it can be debugged.
Compile performs a PL/SQL compilation of the subprogram.
Switch to Read Only / Switch to Write Mode toggles between read-only and read/write mode for the code editing window. (See also the Code Editor user preference Start in Read Only Mode.)
Profile displays the Run/Debug/Profile PL/SQL dialog box.
The Debugging - Log tab under the code text area contains the debugging toolbar and informational messages. The debugging toolbar has the icons shown in the following figure.
Find Execution Point goes to the execution point (the next line of source code to be executed by the debugger).
Step Over bypasses the next subprogram (unless the subprogram has a breakpoint) and goes to the next statement after the subprogram. If the execution point is located on a subprogram call, it runs that subprogram without stopping (instead of stepping into it), then positions the execution point on the statement that follows the call. If the execution point is located on the last statement of a subprogram, Step Over returns from the subprogram, placing the execution point on the line of code that follows the call to the subprogram from which you are returning.
Step Into executes a single program statement at a time. If the execution point is located on a call to a subprogram, Step Into steps into that subprogram and places the execution point on its first statement. If the execution point is located on the last statement of a subprogram, Step Into returns from the subprogram, placing the execution point on the line of code that follows the call to the subprogram from which you are returning.
Step Out leaves the current subprogram and goes to the next statement.
Step to End of Method goes to the last statement of the current subprogram.
Resume continues execution.
Pause halts execution but does not exit, thus allowing you to resume execution.
Terminate halts and exits the execution. You cannot resume execution from this point; instead, to start running or debugging from the beginning of the subprogram, click the Run or Debug icon in the Source tab toolbar.
The Breakpoints tab displays breakpoints, both system-defined and user-defined.
The Smart Data tab displays information about variables, using your Debugger: Smart Data preferences. You can also specify these preferences by right-clicking in the Smart Data window and selecting Preferences.
The Data tab displays information about variables, using your Debugger: Data preferences. You can also specify these preferences by right-clicking in the Data window and selecting Preferences.
The Watches tab displays information about watches (see Setting Expression Watches).
For more information about developing, compiling, and using PL/SQL functions and procedures, see Subprograms and Packages: Usage Information.
If the function or procedure to be debugged is on a remote system, see also Remote Debugging.
Related Topics
Using Snippets to Insert Code Fragments
Using Bookmarks When Editing Functions and Procedures
Run/Debug/Profile PL/SQL (dialog box)
Debug a PL/SQL Procedure (tutorial)
Displaying SQL Trace (.trc) Files
Using the PL/SQL Hierarchical Profiler
Subprograms and Packages: Usage Information
SQL Developer Concepts and Usage
When you are editing a long function or procedure, you may find it convenient to create bookmarks in the code so that you can easily navigate to points of interest.
To create or remove a bookmark, click Navigate, then Toggle Bookmark. When a bookmark is created, an icon appears to the left of the thin vertical line.
To go to a specific bookmark, click Navigate, then Go to Bookmark. To go to the next or previous bookmark, click Navigate, then Go to Next Bookmark or Go to Previous Bookmark, respectively.
To remove all bookmarks from the currently active editing window for a function or procedure or from all open editing windows, click Navigate, then Remove Bookmarks from File or Remove All Bookmarks, respectively.
You can also go to a specific line or to your last edit by clicking Navigate, then Go to Line or Go to Last Edit, respectively.
Remote debugging allows you to debug a procedure that is initiated from a program other than SQL Developer. You can also use remote debugging to debug stored procedures initiated from computers other than your local system.
Remote debugging involves many of the steps as for local debugging; however, you need to set up the SQL Developer debugger to listen. Do the following before you start the remote debugging:
Do one of the following:
Use an Oracle client such as SQL*Plus to issue the debugger connection command. Whatever client you use, make sure that the session which issues the debugger connection commands is the same session which executes your PL/SQL program containing the breakpoints (and be sure each breakpoint is an executable line, not a declaration). For example, if the name of the remote system is remote1, use the following SQL*Plus statement to open a TCP/IP connection to that system and the port for the JDWP session:
EXEC DBMS_DEBUG_JDWP.CONNECT_TCP('remote1', '4000');
The first parameter is the IP address or host name of the remote system, and the second parameter is the port number on that remote system on which the debugger is listening.
Define the ORA_DEBUG_JDWP operating system environment variable. For example:
ORA_DEBUG_JDWP=host=mypc:port=1234
Right-click the connection for the remote database, select Remote Debug, and complete the information in the Debugger - Attach to JPDA dialog box.
Then, follow the steps that you would for local debugging (for example, see Debug a PL/SQL Procedure).
If you have any SQL Trace (.trc) output files, you can display them in SQL Developer as an alternative to using the TKPROF program to format the contents of the trace file. To open a .trc file in SQL Developer and see an attractive, effective display of the information, click File, then Open, and specify the file; or drag the file's name or icon into the SQL Developer window.
You can then examine the information in the List View, Statistics View, and History panes, with each pane including options for filtering and controlling the display.
For information about SQL Trace and TKPROF, see Oracle Database SQL Tuning Guide.
For an Oracle Database Release 11.1 or later connection, you can use the PL/SQL hierarchical profiler to identify bottlenecks and performance-tuning opportunities in PL/SQL applications. Profiling consists of the two steps: running the PL/SQL module in profiling mode, and analyzing the reports. In addition, some one-time setup work is required the first time you use profiling in SQL Developer.
To initiate profiling, right-click the name of the function or procedure in the Connections navigator hierarchy and select Profile, or click the Profile button on the PL/SQL source editor toolbar. After the function or procedure is run in profiling mode, the profiler reports are located at the Execution Profiles tab of the object viewer window. You can review subprogram-level execution summary information, such as:
Number of calls to the subprogram
Time spent in the subprogram itself (function time or self time)
Time spent in the subprogram itself and in its descendent subprograms (subtree time)
Detailed parent-children information, including all subprograms that a given subprogram called (that is, children of the given subprogram)
For more information about using the PL/SQL hierarchical profiler, see Oracle Database Development Guide.
A watch enables you to monitor the changing values of variables or expressions as your program runs. After you enter a watch expression, the Watches window displays the current value of the expression. As your program runs, the value of the watch changes as your program updates the values of the variables in the watch expression.
A watch evaluates an expression according to the current context which is controlled by the selection in the Stack window. If you move to a new context, the expression is reevaluated for the new context. If the execution point moves to a location where any of the variables in the watch expression are undefined, the entire watch expression becomes undefined. If the execution point returns to a location where the watch expression can be evaluated, the Watches window again displays the value of the watch expression.
To open the Watches window, click View, then Debugger, then Watches.
To add a watch, right-click in the Watches window and select Add Watch. To edit a watch, right-click in the Watches window and select Edit Watch.