Remotely Debugging PL/SQL Programs

(Remote PL/SQL debugging is only supported with Oracle9i Release 2 databases.)

The main difference between remote debugging and local debugging PL/SQL programs is how you start the debugging session. For remote debugging, you must manually launch the program you want to debug with an Oracle client such as SQL*Plus, Dbms_Job, an OCI program, or a trigger firing. You must then establish the connection from the database program you want to debug (debuggee) to the JDeveloper debugger. Once the debuggee is launched and the JDeveloper debugger is attached to it, remote debugging is very similar to local debugging.

See About Debugging PL/SQL Programs and Java Stored Procedures for information about how to use the debugger with PL/SQL programs and Java stored procedures in the Oracle database and make sure that you've completed the documented prerequisites.

To remotely debug a PL/SQL program using JDeveloper:

(Optional) If you don't already have one, create a database connection using the Database Wizard.
(Optional) If you don't already have one, create a workspace and project.
Choose Tools | Project Properties - Debugger - Remote and select the following options:
1. Remote Debugging check box
2. Listen for JPDA radio button
3. Database Connection for Locating PL/SQL Source - select the appropriate database connection which enables JDeveloper to locate the source code when you step into a PL/SQL call.
In the Navigator, expand the Database node to display the specific database user name and schema name.
Expand the Schema node.
Expand the the appropriate node depending on what you are debugging: Procedure, Function, or Package Body.
Select the procedure, function, or package that you want to debug and double-click to open it in the Code Editor.
In the Code Editor, set a breakpoint in your PL/SQL code by left-clicking in the margin.
Select the appropriate Project icon in the Navigator and press the Debug toolbar button.
In the displayed dialog, enter the appropriate listening port number and click OK. You can choose any valid port number that is not in use by another process. In this example, the port number used is 4000.
Notes:
- If you want to bypass this dialog the next time you are debugging on this port, select the Save Parameters check box from this dialog.
- In the Run Manager window, you should see a satellite dish icon which indicates that the debugger is listening for debugging connections.
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. For example, if you are using SQL*Plus, issue the following commands to open a TCP/IP connection to the designated machine and port for the JDWP session:
EXEC DBMS_DEBUG_JDWP.CONNECT_TCP( '123.456.789.012', '4000' )

where 123.456.789.012 is the IP address or host name where JDeveloper is running, and 4000 is the port number on which the debugger is listening.

From this point on, when you make a call to the PL/SQL code containing the breakpoint, the JDeveloper debugger is activated.

When the debugger accepts a debugging connection, the new debugging process is reflected in the Processes folder in the Run Manager. Also, the Log window should display a message similar to the following:
Debugger accepted connection from remote process on port 4000.

In addition, notice that the layout in JDeveloper has switched from Design layout to Debugging layout (bottom-right of window). Also, the debugging windows including Stack, Data, and Watches, should now be visible.

In the Run Manager, the satellite dish icon indicates that the port is continuing to listen and can accept multiple debugging connections.
Back in the Oracle client, issue a command which invokes the PL/SQL program unit containing your breakpoint. For example, in SQL*Plus, issue a command similar to the following:
EXEC FOO;

where FOO is the name of a PL/SQL procedure.
JDeveloper halts the execution at the first breakpoint (providing this was set in the Start Debugging Option in Project Properties) and displays the state in the debugger windows.
Step into and resume debugging the PL/SQL procedure until you are satisfied. See Moving Through Code While Debugging.
When you are finished debugging, disconnect the debuggee using the disconnect command. For example, from SQL*Plus, enter:
EXEC DBMS_DEBUG_JDWP.DISCONNECT;

The following message appears:

Debugger disconnected from remote process.
To terminate the listening port, right-click the satellite dish icon in the Run Manager and choose Stop Listening from the context menu.