The standard manner to debug any APEX application is done with wwv_flow.debug which execute when the page is run by a developer in debug. Wouldn't it be nice to have a full stack of the code executing with inspection? Read on to see how to initiate a full debug session in SQL Developer from an APEX based application.
There's some setup involved the form of persmission which have to be granted to the user who owns the code as well as the user who is configured to run APEX.
First grant the ability to connect a debug session to the parse_as in APEX. In this case klrice.
SQL> grant DEBUG CONNECT SESSION to klrice;
Grant succeeded.
Second grant the ability to debug to the user configured to run APEX in mod_plsql. Since I'm using XE as my example it's public.
SQL> grant debug on klrice.ls to public;
Grant succeeded.
Now everything is setup and on to the APEX application. This is a very simple example but should show enough of how to make it more generic.
Here's a screenshot of my page.
There is nothing special in the plsql region except it's a plsql region running a very small procedure name LS which loops and does a HTP.P. This procedure needs to be compiled for debug.
Next there's a checkbox for flagging when to connect for debugging and not. Then the real debugger code is in the Processes. The first is a Before Header to be able to debug anything executed on the page. This process is conditional based ont he value of the P1_DEBUG checkbox as follows:
dbms_debug_jdwp.connect_tcp('localhost',4000);
While localhost is hardcoded something like owa_util.get_cgi_env('REMOTE_ADDR') could be used to make it more generic. Also note the port is hardcoded to 4000 , this is important in a sec.
Since the debugger would now be connected, it's nice to disconnect so that's done like this in the After Footer Process:
dbms_debug_jdwp.disconnect;
:P1_DEBUG := 'N';
I've also reset the value of P1_DEBUG to force the checkbox to be choosen each time.
Now into SQL Developer. Right Click on the connection to the user which is the same as the APEX application and choose Remote Debug
Now to bring it all together. Run the application check the checkbox to start the debugger.
This is what should result in SQL Developer:
I hope this helps in the much enjoyed task of bug fixing.