Friday, January 14, 2011

Debugging PL-SQL calls from Java Session Using Eclipse and SQL Developer



Suppose I have a java class PriceInquiryPLSQLOperation which is calling a PL/SQL package PRICE_INQUIRY_SQL.GET_PRICE_DETAILS(?, ?, ?). Currently while debugging some issue, the developer can only debug till the java call from java side and debug the PL/SQL call separately. After reading this document you will be able to debug the PL/SQL package using eclipse and SQL Developer from the same java session.

Before you start debugging your package

  • Open the package in Oracle SQL developer. Right click both body and spec and compile for DEBUG.

  • If the package is using some user defined type objects as input or output then compile those objects for DEBUG.
  • Go to tools -> Preferences -> Debugger.  Select the option Prompt for Debugger host for Database Debugging.


  • Right click the DB connection and start the remote debug session.

  •  It will open the Listen for JPDA dialog. In Local address enter the ip address of the machine and hit OK.

  • This will start the debug listener as follows.

  • Now in the java program calling the PL/SQL package before the actual PL/SQL call enter the following code

//For debugging only
        CallableStatement debugStatement = getCallableStatement("begin DBMS_DEBUG_JDWP.CONNECT_TCP( '<ipaddress provided above>', 4000 ); end;"); //Note the ipaddress and port – Same as the values provided in the previous dialog.
        debugStatement.execute();

        // execute the actual PL/SQL call
  statement.execute();
  • Now open the PL/SQL package and put a break point where you want to debug something.
  •  When you invoke the  PL/SQL program from Java

  • It will stop at the first break point in PL/SQL as follows. Note that you can see the values passed by Java in the user defined object. The objects appearing as opaque needs to be recompiled for debug. Now you can debug through each line in PL/SQL much like java code.


 Enjoy – Happy PL/SQL Debugging. Let me know if there is any question. 

11 comments:

  1. //For debugging only
    CallableStatement debugStatement = getCallableStatement("begin DBMS_DEBUG_JDWP.CONNECT_TCP( '<ipaddress provided above', 4000 ); end;";); //Note the ipaddress and port – Same as the values provided in the previous dialog.
    debugStatement.execute();

    // execute the actual PL/SQL call
    statement.execute();

    May be this code can be written in an interceptor...

    I am also working in Oracle.. Probably we could sit an make some solution for this

    ReplyDelete
  2. Thanks for nice article, very helpful in debugging pl/sql code.
    I have written similar one here http://www.ramraje.com/database/debugging-oracle-plsql-code-from-java-call-using-sql-developer/

    ReplyDelete
  3. Hi, I am getting below exception while calling PL / SQL package.

    Caused by: java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_DEBUG_JDWP".

    Your immediate reply would be highly appreciated.
    Thanks in advance.. :)

    ReplyDelete
    Replies
    1. Ask your DBA to grant the permission

      Delete
  4. "In Local address enter the ip address of the machine and hit OK"
    Which machine? Where the db instance is? Or the one that I'm running eclipse and sqldev at?

    ReplyDelete
  5. Nice, very helpful.

    ReplyDelete
  6. Thanks for saving lots of time

    ReplyDelete