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.