Using Oracle’s SQL Developer it’s possible to debug an anonymous PL/SQL-block as of version 3. It’s not easy to find out how, though, and it works quite quirky: this feature clearly needs some more attention of the SQL Developer team at Oracle. This scenario was written based on SQL Developer version 3.1.07. So for all you searchers out there: here goes!
When you’ve written an anonymous block (script or worksheet containing PL/SQL that’s not stored in the DB) and you want to debug, first thing to check is the SQL Developer preferences (menu Tools -> Preferences -> Debug) and make sure the ‘Start Debugging Option’ is set to ‘Step into’. This is a vital step, if the option is set to anything else this will not work!
Step 1
Open your code-to-be-debugged (be it from file or unsaved) in a fresh SQL worksheet. NOTE: I only managed to get this working by pasting the code from a script into a fresh worksheet, debugging a script opened from file didn’t work.
It’s no use trying to set breakpoints now, this will not work. Don’t worry, you will be able to set them later on. Just continue.
Step 2
Start the debugging session by pressing ctrl+shift+F10 (or the appropriate shortcut-key). NOTE: For reasons unknown to me, the shortcut-key is sometimes unavailable, but selecting ‘Debug’ from the right mouse button context menu will work if this happens to you too.
The debugging will start, and execution will halt at the first line, thanks to the Step Into option.
Step 3
Now it will be possible to set breakpoints by either clicking in the margin or pressing the shortcut-key. NOTE: the debugger will open the code in a new worksheet, titled ‘ANONYMOUS_BLOCK’. If the debugging has ended, this worksheet will stay open, but you’ll not be able to start a new debugging session from this window. Make a habit of closing the worksheet after debugging, so you’ll not be stumped wondering why the debugging doesn’t work anymore, while it did five minutes ago… (been there!)
Step 4
Debugging can now continue just like debugging stored code. By the way: did you know that using watches, you can change the values of variables during debugging? You can!
As stated before, close the ANONYMOUS_BLOCK worksheet after debugging. Your original code will still be there in the worksheet you first opened it in.
Happy debugging!
Ronald's Oracle 