This article presents how to use remote debugger in Sqldeveloper.

It’s recommended to read article about debugging code in sqldeveloper:

Sqldeveloper debugger

Remote debuger is very nice feature of sqldeveloper. It gives possibility to a Developer to debug a code started in different session by a Client.So somebody else (on completly different computer) is starting code and we just debug it. It’s like setting a bait(by Developer) and wait for careless animal 🙂 (Client).

Remote debug steps:

1. Developer – compiles a PL/SQL code in debug mode

2. Developer – starts remote listener debugger

3. Developer – sets breakpoint(s) in code

3.Client – establishes connection with remote listener debugger. This can be done as well inside code of a debugged code.

4. Client – starts code

5. Developer – waits and traps code executed by client. So developer will take over control on execution of code

Privileges

To debug using remote debugger you need following privileges

DEBUG CONNECT SESSION
DEBUG ANY PROCEDURE

Compile procedure/function is debug mode

CREATE OR REPLACE PROCEDURE my_code
 IS
 v_id NUMBER := 5;
 BEGIN
 FOR i IN 1..v_id
 LOOP
 DBMS_OUTPUT.put_line(i);
 END LOOP;
 END;
 /
 
ALTER PROCEDURE my_code compile debug;

Run remote listener debuger

Just right click on your connection and select “Remote Debug” option

Then you should set following parameters for you listener. The listener should be listening on your computer.

Local address: Name or IP address of the remote host on which SQL Developer should listen for the database to connect.The IP address should be visible in your network.

Port: Listening port number on the remote host. You can choose any valid port number that is not in use by another process.

Timeout: The number of seconds that SQL Developer will wait for the remote database to make a debugging connection

Now you should see that remote dubuger is waiting for incoming connections

Set breakpoint for debuged code

In this step we need to open procedure “MY_CODE” and set breakpoint in line 8.

Establish connection from client to listener

Log on into database as Client and run following code

exec DBMS_DEBUG_JDWP.CONNECT_TCP( '192.168.1.90', 4000 );

It establishes connection between Client and remote listener debuger. Developer will see this new session in sqldeveloper (Run Manager – processes). There are additional actions with such connections like “Detach”, “Terminate”, “View log”.

In this step we are not yet debuging code !

Client starts code

Just run code following code as Client.

exec my_code

Client session should hang because control is taken over by Developer.

Debug code as Developer

Now as Develolper you can investigate what’s is going on inside code.

IMPORTANT NOTE – sometimes it’s very difficult to establish connection from Client to remote listener debuger because it would require to change code in client application like Oracle Forms. In such case you can add establishing connection with listener inside of procedure code. Remember in such case any session who calls the procedure will establish connection to listener.

CREATE OR REPLACE PROCEDURE my_code
IS
  v_id NUMBER := 5;
BEGIN
  DBMS_DEBUG_JDWP.CONNECT_TCP( '192.168.1.90', 4000 );
  FOR i IN 1..v_id
  LOOP
    DBMS_OUTPUT.put_line(i);
  END LOOP;
END;
/

Have a fun 🙂

Tomasz