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:

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.

This is what I want to debug.

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

This is the IP address of YOUR machine.

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('', 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.

Now we’re debugging!

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.

The default is ‘Run until a breakpoint occurs’

Need a general overview/refresher?

Here’s the movie:

Less than 8 minutes 🙂

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Write A Comment