This discussion is archived
14 Replies Latest reply: Sep 2, 2009 4:29 AM by 670770 RSS

Remote debugging with APEX and SQL Developer

Norbert2 Newbie
Currently Being Moderated
Hi,
I have problems concerning breakpoints within my SQL Packages I want to debug.
I want to force SQL-Developer to suspend execution of a function, so I can get forward step by step.
The SQL-Developer do not stop executing at the breakpoints I set in the Package.
The message I get is the following (on SQL-Developer side):

Debugger accepted connection from remote process on port 4000.
Processing 110 classes that have already been prepared...
Finished processing prepared classes.
Debugger disconnected from remote process.

Seems to me, that the process runs into the debug-mode but leave without stopping at a given breakpoint??

Do anybody have an idea how to solve it?

We use Oracle 10.2.0.3.0, SQL Developer 1.5.1 and APEX 3.1.2.
The Package-function I want to debug runs on another Oracle database as the APEX application.

Thanks in advance

Regards
Norbert

Edited by: Norbert2 on Apr 20, 2009 5:47 AM
  • 1. Re: Remote debugging with APEX and SQL Developer
    Carsten Czarski Journeyer
    Currently Being Moderated
    Hi Norbert,

    a frequently issue with debugging and APEX is the privilege for APEX_PUBLIC_USER ... You need to grant

    * DEBUG CONNECT SESSION to the parsing schema of your app
    * DEBUG ANY PROCEDURE to APEX_PUBLIC_USER (or ANONYMOUS when using the embedded gateway)

    Does this help

    Best regards

    -Carsten

    BTW: A complete german Howto is here
    http://www.oracle.com/global/de/community/tipps/remote-debug/index.html
  • 2. Re: Remote debugging with APEX and SQL Developer
    Norbert2 Newbie
    Currently Being Moderated
    Hi Carsten,
    thank you for the answer.
    This works fine, if the APEX application and the package to debug is on the same DB.
    But I call the package via a DB Link CE06 (target DB):
    :P200_ANZAHL := NOB_PORTAL_MAT.NOB_Report_PF@CE06.ENTW (:APP_USER, :P200_CLASS_ID, :P200_VIEW, to_date(:P200_DATE,'DD-MM-YYYY'));
    What do I have to do on the target DB, to force the debugger to suspend execution in Package.Function "NOB_PORTAL_MAT.NOB_Report_PF"?
    - GRANT DEBUG CONNECT SESSION to ANONYMOUS and
    - GRANT DEBUG ANY PROCEDURE to ANONYMOUS
    on the target DB CE06 doesn't work.
    I only get the message (mentioned above), which shows me, that debugger accepted remote process.

    Maybe you have an idea to who I have to grant the DEBUG privilege on my target DB?

    Thanks in advance

    Regards
    Norbert
  • 3. Re: Remote debugging with APEX and SQL Developer
    Carsten Czarski Journeyer
    Currently Being Moderated
    Hi Norbert,

    how is the DB Link created ...? Which authentication model was being used ...?

    -Carsten
  • 4. Re: Remote debugging with APEX and SQL Developer
    Norbert2 Newbie
    Currently Being Moderated
    Hi Carsten,
    here I have the SQL-statement which creates the link:
    CREATE DATABASE LINK "CE06.ENTW"
    CONNECT TO "ZFF_ENTW" IDENTIFIED BY VALUES '059B7210BA1EC6D7C1DB0A1AA53BACE17D5BBDEEC7D16168ED'
    USING '(DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = frd-orace06.emea.zf-world.com)(PORT = 1530))
    )
    (CONNECT_DATA =
    (SID = CE06)
    )
    )';
    I'm not the database admin, but hopefully you have an idea and could explain it to me, so I can go with the answer to the responsible DB admin.

    Thanks

    Norbert

    Edited by: Norbert2 on Apr 20, 2009 7:14 AM
  • 5. Re: Remote debugging with APEX and SQL Developer
    Carsten Czarski Journeyer
    Currently Being Moderated
    Hi Norbert,

    then the "connect" to the remote database is done with the user ZFF_ENTW. So I'd recommend
    to grant both privileges on the remote DB to this user ...

    grant DEBUG CONNECT SESSION to ZFF_ENTW;
    grant DEBUG ANY PROCEDURE to ZFF_ENTW;

    I've never done remote debugging via a database link so I'm keen on the results also :-)

    Best regards

    -Carsten
  • 6. Re: Remote debugging with APEX and SQL Developer
    Norbert2 Newbie
    Currently Being Moderated
    Hi Carsten,
    unfortunately this DOESN'T work.
    I checked all the grants for the user ZFF_ENTW on my remote DB, so I'm sure he has "DEBUG CONNECT SESSION" and "DEBUG ANY PROCEDURE" (additionaly ask the DB admin!).
    Do you have any other idea. For me It seems only a little step to make it work, because I can see that the debugger is triggered from my APEX app...
    Or do I have to accept, that in this scenario I cannot use the debugger?

    Thanks for more ideas.

    Norbert
  • 7. Re: Remote debugging with APEX and SQL Developer
    Carsten Czarski Journeyer
    Currently Being Moderated
    Hi Norbert,

    ok ... just to check ...

    * You connected with SQL Developer's remote debugging dialog als ZFF_ENTW to the remote (non apex) database ...?
    * Then you started the procedure via the database link ...?

    * Does debugging work when you start the procedure directly on the remote database (without going through the APEX application) ...?

    A step by step approach might make sense ... first try to remote debug without APEX ... therefore create a debugging
    session to the remote database and start the procedure via SQL Plus .... after that start it also with SQL Plus but from
    the APEX database and finally start with from the APEX application ...

    Then we should see where the problem is ...

    Best regards

    -Carsten
  • 8. Re: Remote debugging with APEX and SQL Developer
    Norbert2 Newbie
    Currently Being Moderated
    Hallo Carsten,
    ok, first about your question, so you can make a picture about our environment:
    q: * You connected with SQL Developer's remote debugging dialog als ZFF_ENTW to the remote (non apex) database ...?
    a: yes
    q: * Then you started the procedure via the database link ...?
    a: yes, within my APEX-application. The procedure is embedded in a DB-package.
    q: * Does debugging work when you start the procedure directly on the remote database (without going through the APEX application) ...?
    a: If I start the procedure directly on the remote database debugging worked.
    Then I check, if it will work, if I start the procedure in the DB-package from the APEX DB (via the DB link).

    And unfortunately this DOESN'T work!

    Even with enabling the remote debugging on the remote DB (where the DB-procedure will be started) and enable the remote debugging in my calling procedure on the APEX DB with the
    dbms_debug_jdwp.connect_tcp('10.181.186.216', 4000);
    -command it DOESN'T work.

    Any idea why this constellation do not work?

    Thanks for your patience.

    Regards
    Norbert
  • 9. Re: Remote debugging with APEX and SQL Developer
    Carsten Czarski Journeyer
    Currently Being Moderated
    Hi Norbert,

    sorry for the delay ... I was busy yesterday. Now I tried it myself ...

    1. Create the procedure on the remote DB, DB Link at APEX DB ...
    2. grant the Debug privileges to the User on the remote db (ZFF_ENTW in your case)
    2. Open a SQL Dev Connection to remote DB, set breakpoint and start the remote debugger - SQL Dev is then listening to my desktop pc

    3. the first attempt is SQL*Plus on the local (APEX) DB ... I tried the following script:

    begin
    dbms_debug_jdwp.connect_tcp('[ip address sqldeveloper pc]', 4000);
    cnt@stuamdmuc(100);
    dbms_debug_jdwp.disconnect;
    end;
    /

    ... this did not work ... but this cannot work. Not the APEX database should connect to SQL Dev, the remote DB
    has to connect to SQL DE ... therefore change the script ...

    begin
    dbms_debug_jdwp.connect_tcp@stuamdmuc('10.165.250.114', 4000);
    cnt@stuamdmuc(100);
    dbms_debug_jdwp.disconnect;
    end;
    /

    the DBMS_DEBUG.CONNECT_TCP call has to be executed on the remote site ... this setup then worked!

    I finally created an APEX process which executed the same code ... and it worked as well ...

    So does this help ...?

    -Carsten
  • 10. Re: Remote debugging with APEX and SQL Developer
    Norbert2 Newbie
    Currently Being Moderated
    Hi Carsten,
    I tried, what you supposed to me, but it doesn't work.

    I assume the '@stuamdmuc'-string is your DB-link?

    I want to show what I've done:
    1) write an PL/SQL-script, install it on the APEX-side and try to connect to the debugger of the SQL-Developer
    on my desktop PC, which is connected to the remote DB
    PL/SQL-Script:
    begin
    dbms_debug_jdwp.connect_tcp@ce06.entw('ip-address.sql-dev-pc', 4000);
    nReturn := NOB_PORTAL_MAT.NOB_Report_PF@CE06.ENTW ('F31443', '220431',3,to_date('03-06-2008','DD-MM-YYYY'));
    dbms_debug_jdwp.disconnect;
    end;

    where "@ce06.entw" is my DB-Link from APEX side to the remote DB

    2) activate the remote debugging on SQL-Developer --> start Debug Listener:
    Debug Listener (Port=4000 Timeout=0 Local address=ip-address.sql-dev-pc)

    --> I get a "EXCEPTION_ORA_604"-Message at the "dbms_debug_jdwp.connect_tcp"-command?

    First, I should solve this, before I try to trigger debugging from APEX.

    Regards
    Norbert
  • 11. Re: Remote debugging with APEX and SQL Developer
    Carsten Czarski Journeyer
    Currently Being Moderated
    Hi Norbert,

    exactly ... seems that your CONNECT_TCP command causes an ORA 604 on the remote site:

    ORA-00604: "error occurred at recursive SQL level %s"
    // *Cause:  An error occurred while processing a recursive SQL statement
    //     (a statement applying to internal dictionary tables).
    // *Action: If the situation described in the next error on the stack
    //     can be corrected, do so; otherwise contact Oracle Support.


    which then means that "behind this" another Oracle error occurred ... perhaps the trace files
    give some insight ..

    BTW: Which Oracle versions are involved? My tests were on 11.1.0.7 ...

    Best regards

    -Carsten
  • 12. Re: Remote debugging with APEX and SQL Developer
    Norbert2 Newbie
    Currently Being Moderated
    Hi Carsten,
    first our Oracle Versions:
    On both servers we have Oracle 10.2.0.3.0 Release 2 Patch Set 2

    Content of the trace file from the APEX-DB (hope you can see something):

    /AE01/app/oracle/admin/AE01/udump/ae01_ora_29218.trc
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    ORACLE_HOME = /AE01/app/oracle/product/10.2.0
    System name: Linux
    Node name: frds00527
    Release: 2.6.5-7.282-bigsmp
    Version: #1 SMP Tue Aug 29 10:40:40 UTC 2006
    Machine: i686
    Instance name: AE01
    Redo thread mounted by this instance: 1
    Oracle process number: 33
    Unix process pid: 29218, image: oracleAE01@frds00527

    *** 2009-04-24 09:23:19.975
    *** SERVICE NAME:(SYS$USERS) 2009-04-24 09:23:19.975
    *** SESSION ID:(161.16789) 2009-04-24 09:23:19.975
    *****
    ORA-30687: session terminated by debugger
    ORA-00604: error occurred at recursive SQL level 1
    ORA-06510: PL/SQL: unhandled user-defined exception
    ORA-06512: at "SYS.PBREAK", line 1069
    ORA-06512: at "SYS.PBSDE", line 201
    ORA-30677: session is already connected to a debugger
    ORA-06512: at line 1
    *****

    Thanks in advance for any further contact.

    Regards
    Norbert
  • 13. Re: Remote debugging with APEX and SQL Developer
    Carsten Czarski Journeyer
    Currently Being Moderated
    Hi Norbert,


    30677, 00000, "session is already connected to a debugger"
    // *Cause:  An attempt to connect a session to a debugger could not proceed
    // because the session is already connected to some debugger.
    // *Action: Either use the option to force a connection or first disconnect the
    // session from its existing debugger.

    that's odd ... seems that the session on the remote DB is already connected to (a / the) debugger;
    perhaps cleaning up helps ...

    you might explicitly close the dblink from the APEX site:
    SQL> alter session close database link '[dblink-name]'
    /

    Best regards

    -Carsten
  • 14. Re: Remote debugging with APEX and SQL Developer
    670770 Newbie
    Currently Being Moderated
    Hi Carsten,

    now I have done a further step. I can debug a remote PL/SQL-Package through APEX, but now I get an exception when the program execution jumps back to the APEX PL/SQL-Package.

    My APEX-Page-Process looks like this:
    BEGIN
      dbms_debug_jdwp.connect_tcp(host => 'IP-ADDRESS', port => '4201');
      APEX_TEST_PKG.remoteDebug;
      dbms_debug_jdwp.disconnect;
    END;
    The procedure APEX_TEST_PKG.remoteDebug looks like this (located on the APEX-DB):
    PROCEDURE startRemoteDebug 
      AS  
        v_string    VARCHAR2(50)   := '';
      BEGIN
        
        dbms_debug_jdwp.connect_tcp@REMOTE.ENTW(host => 'IP-ADDRESS', port => '4000',
                 option_flags => dbms_debug_jdwp.connect_defer_suspension);
         
        v_string := REMOTE_TEST_PKG.testFunc@REMOTE.ENTW;
        DBMS_OUTPUT.PUT_LINE(v_string);
       
        dbms_debug_jdwp.disconnect@REMOTE.ENTW;
      END remoteDebug; 
    The procedure REMOTE_TEST_PKG.testFunc looks like this (located on the Remote-DB):
    FUNCTION testFunc RETURN VARCHAR2 
      AS  
        v_string    VARCHAR2(50)   := '';
      BEGIN         
        v_string := 'Test';
       
      RETURN v_string;
    END testFunc; 
    When I start the debug session without the option dbms_debug_jdwp.connect_defer_suspension, I get an exception Oracle.EXCEPTION_ORA_604.
    Starting the debug session with the above mentioned option, I am able to debug the remote function.

    But now I get an exception when the executor tries leaving the remote function going back to the invoking function. With the debugger I can step over the return-statment successfully and the debugger steps on the next line END testFunc;. On this line I get the exception above
    Exception breakpoint occurred at line -1 of PBREAK.pls.
    $Oracle.Builtin.EXCEPTION_USER: 
    Exception breakpoint occurred at line -1 of PBREAK.pls.
    $Oracle.Builtin.EXCEPTION_USER: 
    I tried another way where I get now exception and where I can step through my functions in APEX, descending to functions in the remote db and going back to APEX.
    For this case I put the debug-statements in remote function:
    FUNCTION testFunc RETURN VARCHAR2 
      AS  
        v_string    VARCHAR2(50)   := '';
      BEGIN         
        dbms_debug_jdwp.connect_tcp(host => 'IP-ADDRESS', port => '4000');
        v_string := 'Test';
        dbms_debug_jdwp.disconnect;   
      RETURN v_string;
    END testFunc; 
    But I have no idea why it doesn't work properly starting the remote debug session in the apex procedure.

    Regards
    Michael

    Edited by: user6044915 on 02.09.2009 04:08

    Edited by: user6044915 on 02.09.2009 04:21

    Edited by: user6044915 on 02.09.2009 04:28

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points