After years upon years of using TOAD, I now have the need to actually use the DEBUG feature.
I have granted DEBUG SESSION to public and set "Toggle Compiling with DEBUG". I can now set items in the Debug menu. However, when I click on RUN, it does not stop on the breakpoint I set.
I am obviously doing something wrong. Assistance would be appreciated
The debug_session privilege applies to debugging pl/sql objects (for instance, if you want to step line by line through a procedure).
It looks like here, you are debugging a script.
Your breakpoint is inside a pl/sql block of the script. We probably shouldn't have let you set a breakpoint there, because we can't debug into that block. Put the breakpoint on line 2 or 4 and it will stop. I think it should stop if you put a breakpoint on line 1, but that's not working for me.
Thank you for the prompt reply. I see what you mean.
For my purposes, I created another script file that creates a package. The sample that I showed had, between the PUT_LINEs, a call to a function within that package.
Based on your comment above, I get the sense that I would not be able to step through that code as well.
I have only been doing stuff like this for decades and each IDE poses its own challenges.
Appreciate the help.
You can debug a function within a package like this:
- Double-click the package in the schema browser to open it in the editor
- On the main debug menu, change from Script debugger to DBMS_DEBUGGER
- Put breakpoints where you want them in the package body.
- Click the bug icon on the Editor's toolbar. A prompt will appear like "do you want to compile dependencies with debug?" You can answer no to that if you aren't going to step into any packs/procs/funcs that are external to this package.
- When the "Set parameters" dialog appears, select the proc/func in the package that you want to execute. If there are input variables, you can set them where I highlighted in red (Sorry, I've lazily pictured a proc with no input vars below)!
- Click Execute and it should stop on your breakpoint. You can hover over variables to see their values. Then use the debug menu or toolbar icons to step though your code line by line.
I know that's not exactly what you were trying to do but I hope it helps.
If I need to debug a anonymous PL/SQL block I usually create a dummy procedure like this:
CREATE OR REPLACE PROCEDURE debug
-- Enter PL/SQL Blok here
This allows you to set breakpoints inside the procedure.
As a side note, @JohnDorlon and I had some offline discussion regarding the executable line indicators (blue dots) and the next beta will more intelligently display them based on the selected debugger. Also, anonymous blocks will no longer show the indicators since they cannot be debugged as-is. You need to refer to the suggestion by @dirk.mika1 to debug them.
Thank you Dirk for the suggestion. Good to know. And Michael, always good to know that you continue to make a great product even better.
Just one nit from me. I was able to get it to work although I still need to bang at it so I can get it down pat. However, when I click on the bug icon, I get a ORA-01031 error (insufficient privileges).
I am on a freshly built 19c environment that may not be properly set up (another team member's task, sigh) but can you possibly give me some clue as to what permission's it is referring to.
It would be appreciated.
Thanks again for great support.
If you go to main menu -> database -> Spool Sql -> spool to screen, then all SQL executed by Toad will be collected in a window at the bottom of the screen, so you can see which one is throwing that error.
Thank you. That worked very well and I found the issue although it does not seem to affect my stepping through my code.
I have set up an Oracle type in another schema. In that schema, I can run the followng with no issue:
GRANT EXECUTE on (schema).(type) TO PUBLIC;
GRANT DEBUG on (schema).(type) TO PUBLIC;
ALTER TYPE (schema).(type) COMPILE DEBUG;
However, in the schema I am debugging in, it is that last line that is being called by the TOAD debug process and that is the one that is failing.
I cannot find any other permissions to apply to this type and am at a loss as to why this is so. Again, this may be a lack of knowledge on my part but ....
Again, thanks for your help. I shall be receding back into the woodwork soon.
P.S. I usually bound my schema.type examples with greater than and less that characters. But when I did that, the entire object disappears.
If you are trying to run those commands from a schema that does not own the type, you might need the either the "alter any type" or "create any type" sys priv.
oh, and you can include the angle brackets by also the tick marks that are on the same key with the tilde like this:
Again thank you for the hint.
Have a nice day and the best of the holidays to you and yours.
Ok. Thank you for the assist. Easily shaved hours off of my debugging task. And like most of my issues, it was a small typo with a major impact.
One note. at least with the construction of my package, it seems TOAD cannot read the values of variables in the package body that were declared and defined in the package specification portion.
Is a possible work-around to call a procedure that initializes then in the package body?
Were both the package spec and body compiled with debug?
You can tell by the icon for the package in the schema browser:
This is what I see
Looks like a different view than you posted but the individual sections are not so marked. This is after the spec and the body were compiled together and separately.
It sounds like then, that it's just a limitation of the DBMS debugger (probably Oracle's limitation, not ours). I'm just guessing though. I'll give it a try soon and let you know.