Debugging PLSQL in Toad

Question

I've been creating packages for Oracle db using PL/SQL and i'm trying to find a good way to debug a PL/SQL package without using the "put_line" command, does anyone have some good tips on how to successfully debug a PL/SQL package either on Toad or SQLPlus?

Ametropia

have you looked at Toad's built-in documentation? (Help->Index->keyword"debug") – Polyadelphous

Answer 1

Depending on the version of TOAD, the icons and toolbars will look different, but the process is the same:

1. Make sure the "Toggle compiling with Debug" option is turned on
2. Click "Compile" button
   a. Set a breakpoint
3. Click "Execute PLSQL with debugger"

Toad 9.7:

Toad 11.6:

Profitable

@sharkbait you will need to be granted database privileges that allow you to debug. The exact privileges depend on the Oracle version. – Profitable

Is it possible to save a set of breakpoints in TOAD for later use? I mean... Everytime I restart it I lose them, and I may have a big set of breakpoints in a certain package. Moreover, even if I try not to close the program, it crashes very often and I have to reset them everytime, losing my "strategic" placements and some conditional ones... – Radices

How to disable the package debug? there is no way/button to disable it. – Diploma

@Diploma my screenshot above might be a little dated at this point, maybe the icon changed, but above I highlighted and labelled the debug button "1" - when the button is highlighted indicated it is active and has been clicked, clicking it a second time will de-select it and turn the option off. After clicking the button to toggle it off you need to recompile the package for the change to take effect. – Profitable

Answer 2

First of all, in order to be able to debug PL/SQL code, one must have appropriate database privilege for debugging. (GRANT DEBUG CONNECT SESSION TO user). If you're not granted this privilege, you DB tool (like Quest TOAD) might not even show debugging options or might show it disabled.

Second, prior to debugging, the code (procedure, function or package) it has to be precompiled for debugging. When one compiles the code with debug option, then compiler inserts additional data into compiled code to be able to stop on breakpoints during process of debugging. (Switch on debug option with Toggle compile with Debug and compile your code) After you finish your development phase with debugging, you should recompile your code without debugging option (Switch off Toggle compile with Debug and compile your code).

Then you should insert debugging breakpoints into your code and watches (variables) you want to track in debugger during execution.

Finally you should start your code with debug, the execution will stop on first breakpoint and using debugg toolbar you can step into, step over, run to cursor ... in your code.

Passim

I miss one button for continuing execution until next breakpoint. I find it very annoying to have to manually putting the cursor in the next line I want the debugger to stop when using the "Run to cursor" option. Plus, I don't quite understand how the "Step Over" funtion works. Any hints on these? – Radices

Oh, sorry - I'm replying myself. I just figured out that I need to use the "Execute PL/SQL with debugger" in order to resume execution... D'oh! – Radices

Answer 3

To enable DEBUGING, Grant below privileges' and re-open TOAD. It always works for me.

    GRANT ALTER SESSION TO <databaseUser>;
    GRANT CREATE SESSION TO <databaseUser>;
    GRANT EXECUTE ON DBMS_DEBUG to <databaseUser>;
    GRANT ALTER ANY PROCEDURE TO <databaseUser>;
    GRANT CREATE ANY PROCEDURE TO <databaseUser>;
    GRANT DEBUG ANY PROCEDURE TO <databaseUser>;
    GRANT DEBUG CONNECT SESSION TO <databaseUser>;

Closehauled