This article presents how to use remote debugger in Sqldeveloper.
It’s recommended to read article about debugging code in sqldeveloper:
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
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.
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 🙂