How to Remote Debug PLSQL Code on SQL Developer?

Question

I am working on a java based enterprise application and i am looking for ways to debug the plsql code on sql developer when the request comes from an application.

I am able to remote debug the pl sql code with a test sql program written locally invoking a specific procedure on my database.

i want to be able to achieve the same debug control when the request comes from an application.

By Right clicking on the db connection and clicking on remote debug, i get a 'Listener for JPDA' popup asking me for the port and local address. By entering the ip address of the machine where this application is deployed, I get the error - 'The debugger is not able to listen for JPDA using the specified parameters. Do you want to change the parameters?'

How do i get this working?

Answer 1

As mentioned by MDW, Sue Harper's blog is all you should need.

To set-up, basically, right-click on your connection in SQLDeveloper and select Remote Debug. Enter your database host and port to listen on. In your client code, e.g. in java, add the following:

private void debugPlSql() throws SQLException {
    CallableStatement cstmt = null;
    try {
        cstmt = conn.prepareCall(
            "call DBMS_DEBUG_JDWP.CONNECT_TCP('...my-ip-address...', 16000 )");
        cstmt.executeUpdate();
    } finally {
        // cstmt.close() ...
    }
}

Note my-ip-address is your database server local IP address and 16000 is the debug port I used which brings me back to your question (which is now well out of date - so sorry about that)

I had this issue (alert) and the error isn't all that helpful but, in my case, it was just that the default port 4000 wasn't available. This is why my example uses port 16000 as this was available and worked fine when I changed the port in SQLDeveloper.

I call the debugPlSql prior to executing the sql of interest.

Edit:

Don't forget:

GRANT DEBUG CONNECT SESSION TO myuser;
GRANT DEBUG ANY PROCEDURE TO myuser;