1 Reply Latest reply on Feb 20, 2018 8:10 AM by Jim Smith

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

    scottjhn

      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.