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.