Debugging PL/SQL and Java Stored Procedures with JPDA

In 2003 I published a paper entitled Debugging PL/SQL and Java Stored Procedures with JPDA. Its aim was to describe how to debug PL/SQL and Java code deployed into the database with JDeveloper 9i. Two weeks ago a reader of my blog, Pradip Kumar Pathy, contacted me because he tried, without success, to do something similar with JDeveloper 11g, WebLogic 11g and Oracle Database 11g. Unfortunately I was not able to help him. The reason is quite simple, since 2004 I’m an Eclipse user…

Few days later Pradip contacted me again to let me know that, at last, he succeeded. Here you find his notes…

1. Grant the required privileges

GRANT DEBUG CONNECT SESSION to &&schema_name;
GRANT DEBUG ANY PROCEDURE TO &&schema_name;

2. Create a database connection under “Application Resources” and compile the stored procedure in debug modus (Figure 2a and Figure 2b)
3. Write some Java code to let the database engine connect JDeveloper (this piece of code must be executed before calling the PL/SQL stored procedure)

private static void remotePLSQLDebug(Connection con) throws SQLException
{
  SystemProperties systemProperties = null;
  systemProperties = SystemProperties.getInstance();
  CallableStatement callState = null;
  String port = systemProperties.get("plsqldebug.port").trim();
  String storedCall = "call dbms_debug_jdwp.connect_tcp('localhost'," + port + ")";
  callState = con.prepareCall("{" + storedCall + "}");
  callState.execute();
  callState.close();
}

4. Configure the plsqldebug.port parameter in the application properties
5. Edit the project properties to enable JPDA and defining the port used by the JPDA listener (Figure 5a and Figure 5b)
6. Deploy the EAR in WebLogic and start the JPDA listener configured in the previous step
7. Execute the Java code of step 3; as a result the database engine connects the JPDA listener and a connection process is created (Figure 7)
8. When the PL/SQL stored procedure is executed, JDeveloper is able to debug it (open the PL/SQL stored procedure in the “Database Navigator” for that purpose)
9. Once the debugging is over detach the connection process

Thank you Pradip to share your findings!