Debugging PL/SQL…in APEX

Of course you can debug your PL/SQL from SQL Developer when you start it in SQL Developer.

But what if your PL/SQL is being executed from ‘somewhere else’ – and you want to debug it from there?

That’s known as remote or external or just-in-time debugging, and we support that.

Here are some slides:

Debugging PL/SQL from your APEX Applications with Oracle SQL Developer from Jeff Smith

Setup in a nutshell

You are going to tell your APEX app to connect back down to your machine where SQL Developer is running, so you need to make sure:

your laptop can be reached on the network from where your APEX app (db) is running
if your db is 12c or higher, that there’s an ACL rule in place to let the database reach out on the network
start a remote listener in SQL Developer
have your PL/SQL compiled for debug
have at least one breakpoint set

Here’s what the APEX setup looks like…

We’ll come back to the highlighted code in a minute.

I have a APEX form with some custom processing built-in, which calls this PL/SQL program.

In SQL Developer, I right-click on my connection, and ask for Debug – Remote:

With this listening now, we can almost get started.

Let’s go back and look at the code tied to the in page processing in APEX again.

I’ve added this line:

dbms_debug_jdwp.connect_tcp('192.168.56.1', 4000);

When the database runs this, it’s going to go to that IP address and port, and it’s going to give that machine control of the session once some PL/SQL is executed. And it just so happens the next bit of PL/SQL to be ran is what I want to debug.

So just run your APEX app. Or in other words, ‘exercise the code.’ Then when the PL/SQL is encountered, your app will ‘freeze’, and SQL Developer will immediately take over.

Make sure you have the debugger set to start with a ‘Step’ or you have at least one breakpoint set, or the code will run right through SQL Developer and your application will take back control. It will work, but you wont have noticed any debugging..unless you hit an Exception.