Toad World® Forums

Question about "script debugger"

I’m used to debugging packages, I can’t recall if I’ve ever tried it on standalone functions or procedures, but I assume it would work the same.

I see Burt’s post on TOADWorld about script debugging. Can it do anonymous blocks of PL/SQL or just sql*plus “scripts”.

I have anonymous block that we are running against our production database for a procedure not yet deployed into production, but we are doing production parallel testing before deployment. As such I have taken what will eventually be a standalone procedure in the database, commented out deletes and inserts and replaced them with dbms_outputs to show the results for comparison.

The procedure is having “issues”, I think it is falling into an infinite loop based on some data condition. Is there a way to debug this other than craploads of dbms_outputs? I’m aware of “auto debugging” and that would be better than nothing, but I’m not sure that would work if the process gets into a loop and we have to have the session killed. Wouldn’t I lose access to the dbms_output buffer?

script degugging is really just for sql plus scripts - not PL/SQL and so not anaonymous blocks. You may be stuck with auto debug and the many dbms_output statements it will auto gen for you - sorry …

If your code hangs I think you’re stuck on getting output. What if you create a fake dbms_output package with put_line (temporarily!) and just have it write
the entries to a table or something while you test. Then you can still use auto-debugger.

From: Bert Scalzo [mailto:bounce-Bert_Scalzo@toadworld.com]

Sent: Thursday, August 14, 2014 4:05 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Question about “script debugger”

RE: Question about "script debugger"

Reply by Bert Scalzo

script degugging is really just for sql plus scripts - not PL/SQL and so not anaonymous blocks. You may be stuck with auto debug and the many dbms_output statements it will auto gen for you

  • sorry …

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

There’s a ton of different ways to diagnose something like this, but probably writing to a temporary table would be your best bet, then you don’t have to worry about the buffer. But, even then, you will probably want to put some controls on your loop - for instance, only have it loop thru the first 1000 records.

That’s exactly what I ended up doing just to be able to see what was going on. Turned out it wasn’t in a loop. Bad stats had caused a thirty minute program
to become a 15 hour program. I analyzed the primary table and zing! Back to thirty minutes. Which still sucks, but it beats the crap out of fifteen hours.

From: Michael Staszewski [mailto:bounce-mstaszew@toadworld.com]

Sent: Thursday, August 14, 2014 3:28 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Question about “script debugger”

RE:
Question about "script debugger"

Reply by Michael Staszewski

If your code hangs I think you’re stuck on getting output. What if you create a fake dbms_output package with put_line (temporarily!) and just have it write
the entries to a table or something while you test. Then you can still use auto-debugger.

From: Bert Scalzo [mailto:bounce-Bert_Scalzo@toadworld.com]

Sent: Thursday, August 14, 2014 4:05 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Question about “script debugger”

RE:
Question about "script debugger"

Reply by Bert Scalzo

script degugging is really just for sql plus scripts - not PL/SQL and so not anaonymous blocks. You may be stuck with auto debug and the many dbms_output statements it will auto gen for you

  • sorry …

To reply, please reply-all to this email.

Stop
receiving emails
on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop
receiving emails
on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.