14 Replies Latest reply on Sep 2, 2009 11:29 AM by 670770

    Remote debugging with APEX and SQL Developer

    Norbert2
      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-Oracle
          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
            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-Oracle
              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
                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-Oracle
                  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
                    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-Oracle
                      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
                        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-Oracle
                          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
                            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-Oracle
                              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
                                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-Oracle
                                  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
                                    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