Remote Debugging with SQL Developer

It occurs to me that while we talk about using the remote debug facility in SQL Developer that you may not know how to use it. So in a few steps and with a few screen shots, I'd like to show you how it's done.
(Please note, to see any of the images clearly, just double click on them.)
1. Let's start with a connection to the database. Create a database connection. (File -> New Connection) and complete the details. I only use the basic tab, so don't need to set up tnsnames or anything else. You'll see the database is on my own machine in this example, but it need not be.




















2. Now you can connect to any objects this user owns, using SQL Developer. You can browse the various objects the user has access to. I'm only interested in this procedure, which my user HR owns.














3. You can run and debug this procedure in SQL Developer, but that's not the purpose of the exercise. What we want to do is debug the procedure when it is executed from elsewhere, such as another programme or application. The starting point is to start a remote debug session in SQL Developer. You do this from the Database Connection as shown.














4. A dialog will display, requesting the listening port number and the IP address of the machine running SQL Developer - effectively it's your computer, listening for the database to connect and using that port range to do so. You can set the range of ports through a Preference in SQL Developer. If you run into issues, look out for firewalls between the machines, blocking these ports.













5. Once you have set the remote debug details, you should see the run manager display these.











6. Now you should start a remote session. Using a SQL *Plus command line session will do.

* Invoke SQL *Plus for this user
* In the SQL *Plus session enter the following command:
exec DBMS_DEBUG_JDWP.CONNECT_TCP( '127.0.0.1', 4000 );

You should recognize the parameters from the previous dialog.

If you are debugging an application remotely, then this PL/SQL procedural call will need to go into that application, just for the debug purposes. You'll remove it afterwards.

7. Return to SQL Developer and set a breakpoint in the procedure.
Note: If you are debugging a procedure, you must remember to Compile for Debug, before you can start debugging.














8. Now you need to return to the SQL*Plus session and execute your procedure. If you debug in SQL Developer, an anonymous block is created for you to execute the procedure. In this case you'll need to write one to execute the procedure from SQL*Plus.

Note: In the procedure we have a DBMS_OUTPUT command, so you should also add the 'Set Serveroutput on' command.

9. As soon as you execute the anonymous block, you'll be returned to SQL Developer to debug the session there. Step into the code as you would in a usual debug session.
















10. You can watch data and modify values in the same way as a normal debug session.

In this example, I'll modify the hire date.

11. Once you have reviewed the data, or made the modifications you want, resume debugging. Once the debug session is complete the control is returned to your remote session. In this case, SQL*Plus. You'll notice the modified date reflected in the output.












Quite a long piece, I know, but I think we often veer away from the unknown or untried. Hopefully once you have walked through an example, you might be able to make more use of this very useful feature.