The PL/SQL Debugger Strikes Back: Episode V

The DBA panel in SQL Developer displays sys privs

I’ve already discussed how the Rebel scum go about figuring out what’s wrong with their code. Now I want to get into the nitty-gritty details of how to start a debug session in SQL Developer. There are some prerequisites, but I promise it won’t take but a few moments to setup.

Ensure that

  • you have been granted the DEBUG CONNECT SESSION priv
  • you have EXECUTE privs on DBMS_DEBUG_JDWP
  • you have EXECUTE privs on the object you want to debug

Once you have done ALL of that, you only have one more thing before you actually start debugging. It’s time to COMPILE our PL/SQL object WITH DEBUG. If you do not perform this step, you may find that your breakpoints aren’t honored and that your watches don’t actually watch anything. To compile with debug information, just do this:

Click on the dropdown control next to the compile (gears) button

Oh, I almost forgot to remind you – you have a test/dev environment, right?

You want to mind having any objects compiled for debug in your production environments. It adds a bit of nasty overhead and will impact performance. It won’t show up when you are running it, but if your app is calling it a few hundred times a minute, it will stack up on you in a hurry. So, if you see the little green widget next to any of your PL/SQL objects in production, just be sure to compile it again to remove it.

Alright, you have all the privs, you have compiled for debug, and you’re in a nice, safe, & cozy place to debug (not PROD! Ok maybe sometimes PROD). You are just one or two clicks away.

There is a ‘debug’ button you could hit, but don’t do that yet!

The debug button will start a debug session

Let’s say you DO hit that button. You might be a bit disappointed. You see, the default behavior of the debugger is to run until it hits a breakpoint or an exception. But, we haven’t set any breakpoints yet! If you do this, it will indeed start a debug session and run your program. But, it will also finish and you won’t really see much of anything except for the normal output of your program.

So instead, I want you to open the Preferences dialog under the Tools menu, and go to the Debugger page. Find the ‘Start debugging option’, and change it to ‘Step Over.’

Once you do that, you’ll find that the program is sitting at the first line of executable code waiting for you to take over.

Just one more thing

Before the debug session begins, you’ll see a dialog that looks EXACTLY like the ‘Run PL/SQL’ one. You will need to input any required IN parameters. In addition if you want to debug a package member, be sure you actually select the right target.

And that’s about it for STARTING your debug session. There’s actually another way to start a debug session from OUTSIDE of SQL Developer, but I’m going to save that for another episode. I may even do it in IMAX 3D so I can start to recoup some of these production costs…step over versus into, watches, breakpoints, call stack, etc will be covered in the upcoming post as well.