Remote debugging allows debugging of PL/SQL code in SQL Developer from a remote session. By configuring a listener bound to the IP of the machine on which SQL Developer is running and initializing a connection to this IP from an APEX session, control can be passed to SQL Developer's debugger. This can be very powerful in a situation where a developer is trying to track down a bug in a production (or any other) environment, without having to hack around with the code.
To get started, the following grants are required.
DEBUG CONNECT SESSION to <your APEX parsing schema> DEBUG ANY PROCEDURE to APEX_PUBLIC_USER
Right clicking on a connection in SQL Developer and selecting
Remote Debug will bring up a dialog to capture some details for the listener. Enter the IP of the machine running SQL Developer.
Here I have a test function called
return_me. I want to debug this function. I have added a breakpoint to the gutter.
All that is needed now is for me to initialize the connection to the debug listener in APEX. I'm going to do this right before the
return_me function is called in a page process.
On execution of this process, focus shifts to SQL Developer and the breakpoint is highlighted with values in scope displayed in the debugger panel at the bottom.
Let's take this further. Ideally we need some way to switch debugging on and off easily without having to change the code. There are many ways to approach this problem, but here is a simple implementation.
Configure a Page 0 dynamic action to fire when a session storage item of
REMOTE_DEBUG_IP is set
The value of
REMOTE_DEBUG_ID is fetched into a page item
A connection to a debug listener is attempted on the specified IP (the port could also be configured if required)
Now we can switch remote debugging on/off just by using the console.