- 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:
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!
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.
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.