Starting the debugger CAN be as simple as hitting this button in Oracle SQL Developer:
But, you need to make sure of the following:
- You have compiled for debug
- You have the privs
- You have a breakpoint set OR you have set the debugger preferences to start with a ‘Step Over’
- On Database 12c, you’ve done setup the network access control list
That’s a lot of buts. Let’s plow through them!
Wait, wait, wait. Before you do anything else: start SQL Developer, connect to your database, and load your PL/SQL object into the Procedure Editor (not the worksheet!)
Ok, now we can start.
Compile for Debug
Don’t do this in production – it adds overhead for working with and executing the PL/SQL. But you really need to do it in the development instance if you want to debug. It’s the default ‘compile’ operation in SQL Developer. We assume you want to DEBUG with the IDE. But if you want to be explicit, do this:
Doing this will allow for things like watches and breakpoints to work. Without those, debugging is kind of boring.
You have the privs.
You need EXECUTE on the object and DEBUG CONNECT SESSION.
Set a breakpoint.
Find an executable line of code. Not a comment. Not a declaration (unless it’s also an assignment.) Now click in the gutter space.
Wait, I don’t want a breakpoint!
Ok, open the preferences and set your debugger to do this:
With it set to ‘Step Over,’ the debugger will stop on Line 1. From there, the debug session is up to you. If you have it set to ‘Run Until Breakpoint Occurs,’ then the procedure will run until it finds a reason NOT to run. Without exceptions or breakpoints, you’ll just see the debugger log say, ‘Finished’ – and that’s no fun.
So set a breakpoint, or don’t set a breakpoint – but make sure the debugger is set to do what you want it to.
And Now You’re Cooking With Gas
Ok, NOW hit the debug button.
You’ll get prompted for the inputs in the anonymous block popup editor. Hit OK, and you’re debugging.
One More Thing…Database 12c
This may be my last post on the debugger, but I doubt it.