This content has been marked as final. Show 14 replies
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
BTW: A complete german Howto is here
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
how is the DB Link created ...? Which authentication model was being used ...?
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 = (PROTOCOL = TCP)(HOST = frd-orace06.emea.zf-world.com)(PORT = 1530))
(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.
Edited by: Norbert2 on Apr 20, 2009 7:14 AM
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 :-)
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.
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 ...
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 ...?
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
-command it DOESN'T work.
Any idea why this constellation do not work?
Thanks for your patience.
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:
dbms_debug_jdwp.connect_tcp('[ip address sqldeveloper pc]', 4000);
... 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 ...
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 ...?
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
nReturn := NOB_PORTAL_MAT.NOB_Report_PF@CE06.ENTW ('F31443', '220431',3,to_date('03-06-2008','DD-MM-YYYY'));
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.
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 188.8.131.52 ...
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):
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
Version: #1 SMP Tue Aug 29 10:40:40 UTC 2006
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.
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]'
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:
The procedure APEX_TEST_PKG.remoteDebug looks like this (located on the APEX-DB):
BEGIN dbms_debug_jdwp.connect_tcp(host => 'IP-ADDRESS', port => '4201'); APEX_TEST_PKG.remoteDebug; dbms_debug_jdwp.disconnect; END;
The procedure REMOTE_TEST_PKG.testFunc looks like this (located on the Remote-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;
When I start the debug session without the option dbms_debug_jdwp.connect_defer_suspension, I get an exception Oracle.EXCEPTION_ORA_604.
FUNCTION testFunc RETURN VARCHAR2 AS v_string VARCHAR2(50) := ''; BEGIN v_string := 'Test'; RETURN v_string; END testFunc;
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
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.
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:
For this case I put the debug-statements in remote function:
But I have no idea why it doesn't work properly starting the remote debug session in the apex procedure.
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;
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