6

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?

Improve this question

4 Answers 4

Reset to default
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;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

When dialog asks you about ip address and port you have to provide the values of machine where debugger is running. I suspect that when you say that you enter ip addres and port of the machine where application is deployed, you mean some another computer.

Another important thing that if you have NAT or firewall between database server and machine where debugger is running you have to enable port forwarding or whitelist your ip:port. When asked about ip and port you have to enter external ip address that is accessible by database server.

There is detailed enough step-by-step guide in the documentation. Although it is for JDeveloper same rules apply for SQL Developer as well.

Improve this answer

Comments

0

  1. In SQL Developer Help search on : Running and Debugging Functions and Procedures Scroll down to Remote Debugging

  2. This blog is from an Internal Oracle person that explains it: http://barrymcgillin.blogspot.com/2012/04/remote-debugging-with-sql-developer.html

  3. Sue use to be the Product Manager for SQL Developer : http://sueharper.blogspot.com/2006/07/remote-debugging-with-sql-developer_13.html

Improve this answer

1 Comment

These links may contain answers to the question, but you should always summarize linked content in your answer so that your answer is still valid if those websites change or become unreachable.
0

I could get it working after installing SQL Developer(non JRE version) and JDK 1.8. And granted necessary privileges (as SYS). Also enabled network ACL to the client IP(where SQL developer is running).

Please refer blog for detailed steps.

http://rajiv-kuriakose.blogspot.in/2016/08/how-to-enable-remote-debugging-in-sql.html

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.