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…
- Grant the required privileges
- Create a database connection under “Application Resources” and compile the stored procedure in debug modus (Figure 2a and Figure 2b)
- 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)
- Configure the
plsqldebug.portparameter in the application properties - Edit the project properties to enable JPDA and defining the port used by the JPDA listener (Figure 5a and Figure 5b)
- Deploy the EAR in WebLogic and start the JPDA listener configured in the previous step
- 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)
- 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)
- Once the debugging is over detach the connection process
GRANT DEBUG CONNECT SESSION to &&schema_name;
GRANT DEBUG ANY PROCEDURE TO &&schema_name;
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();
}
Thank you Pradip to share your findings!
. Hey, I try to optimize my worktime as well. Anyway, both suggested to manually start jserv. When I tried to do so, I received an error (at last). Based on it the problem was self explanatory! The java environment was causing the problem… In fact, with the default installation of CentOS 4.4 only the package java-1.4.2-gcj-compat was installed. After downloading and installing the most recent version of HotSpot (build 1.6.0_12-b04) the problem was solved.
