How to debug Procedures with SQL Developer..?

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.

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.


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 with the
database. You can set the range of ports through a Preference in SQL Developer.

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:
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.

Debugging through SQL developer is always not so easy.
Assume that i have a Procedure where it accepts Oracle types as In Parameter.
Then you will be end up with creating the Oracle Types, which is quiet tedious job.
Or there might be scenario where the values are coming from a java application , so you need to check in run time what the exact values are, then you are in trouble : (

don't worry i have a solution for it.
Make sure your package and procedure has been complied in Debug mode.

JAVA Code for connecting to Remote debug listener:
// Always use as basic JDBC connection , Connection Pooling Objects are not recommended as you //may end up listener problems in DB.and make sure it is disconnected from listener properly

con = DriverManager.getConnection ("jdbc:oracle:thin:@176.6.35.135:1530:DCMD05",
"OPS$ANTMANAG01", "OPS$ANTMANAG01"); // your basic JDBC connection.
// Connect to SQL developer debug listener..

PreparedStatement preparedStatement = null;
StringBuffer query = new StringBuffer(
"begin dbms_debug_jdwp.connect_tcp('176.6.66.104',4000);end;");
try {
preparedStatement = con.prepareStatement(query.toString());
preparedStatement.executeQuery();
preparedStatement.close();
preparedStatement = null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

// you will be automatically transferred to SQL Developer where you can see the run time /parameters in the SQL developers.

//For Disconnecting from debug listener
// Please perform this as a mandatory task

PreparedStatement preparedStatement = null;
StringBuffer query = new StringBuffer(
"begin dbms_debug_jdwp.disconnect;end;");

for Oracle Types make sure the Inner Oracle Types are also complied in Debug mode.

Happy Debugging Procedures, Hope this reduces your strain : )