Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

scottjhnFeb 19 2018 — edited Feb 20 2018

Oracle 11g (running on Linux)

Eclipse (MyEclipse 2017)  and SQL Developer (4.2.0.17)  (both running on Windows 7)

Suppose I have a java class which calls a PL/SQL package pkg_test.get_user_info(?).  I want to debug the PL/SQL package using eclipse and SQL Developer from the java session.

A.  In the SQL Developer

1) Login using SYS as sysdba, then

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

ALTER SESSION SET PLSQL_DEBUG=TRUE


2) Open the package.  Right mouse click on both the body and the spec, and compile for DEBUG.  Then


procedure
get_user_info(p_rc out sys_refcursor)    is      
l_cur_string varchar2(100);        
begin     

l_cur_string := 'SELECT * from customer';   --Set breakpoint on this line      
open   p_rc for l_cur_string;                
exception           
when others then           
raise; 
end;

3) Go to tools -> Preferences -> Debugger.  Selected the option Prompt for Debugger host for Database Debugging.

4)
Right mouse click on  the DB connection,  and choose the remote debug session.

5)    In the opened dialog popup window:


Port: 4000

TImeout: 0

Local address: 192.168.1.95    //Where the Eclipse and SQL Developer are running

Click OK to start the debug listener.



B. In Java ProgramConnection conn = null;
CallableStatement call = null; CallableStatement callDebug = null; ResultSet rs = null;
GRANT DEBUG CONNECT SESSION TO myuser;GRANT DEBUG ANY PROCEDURE TO myuser;

conn = DriverManager.getConnection("jdbc:oracle:thin:@DB_SERVER_IP:1521:myDB", "dbUser", "userPassWord");
try { 

//FIRST CALL DEBUGGER
callDebug = conn.prepareCall("{call DBMS_DEBUG_JDWP.CONNECT_TCP(\"192.168.1.95\", 4000 )}");
//ALSO TRIED  conn.prepareCall("begin DBMS_DEBUG_JDWP.CONNECT_TCP(\"192.168.1.95\", 4000 ); end;");
callDebug.execute();   

/*IT FAILED ON THE ABOVE LINE:
java.sql.SQLException: ORA-06550: line 1, column 36: PLS-00201: identifier '192.168.1.95' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored
*/
callDebug.close();

//THEN THE DESIRED CALL

call = conn.prepareCall("begin pkg_test.sp_get_data(?); end;");

call.registerOutParameter(1, OracleTypes.CURSOR);

call.execute();

rs = (ResultSet) call.getObject(1);

while (rs.next()) {

String username = rs.getString("USERNAME");

}

--What could be wrong?

Scott

NOTE: without calling the callDebug.execute(); the java codes worked without an issue and the correct resultset returned.

This post has been answered by Jim Smith on Feb 20 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 20 2018
Added on Feb 19 2018
1 comment
1,346 views