RE: Re: DMBS_OUTPUT

We affectionately named that internally after the man who came up with the idea - the "B.S. Debugger"



From: [email protected] [mailto:[email protected]] On Behalf Of Bert Scalzo
Sent: Friday, March 22, 2013 9:01 AM
To: [email protected]
Subject: RE: [toad] Re: DMBS_OUTPUT




Toad also offers a cool feature whereby you have toad auto instrument the code (i.e. auto magically inserts a bunch dbms_output.put_line commands) and when you're done working toad can auto remove that code.


From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Norman Dunbar
Sent: Friday, March 22, 2013 4:07 AM
To: [email protected]<mailto:[email protected]>
Subject: Re: [toad] Re: DMBS_OUTPUT



Morning J M,

On 21/03/13 18:41, J M wrote:

> Script Output DOES NOT POPULATE until after the script is run.

DBMS_OUTPUT "stuff" is buffered each time yo make a call to it, the
information you want "printed" is appended to the buffer. When your code
completes, Toad and/or SQL*PLUS can then access the buffer contents and
print it out.

Depending on your database version, there are limites to how much data
you can send. Up to 10g it was 1 million bytes (not necessarily
characters) and in order to get that much you had to "set serveroutput
on size 1000000" at some point (or call dbms_output.enable(1000000) in
PL/SQL). Excess data is simply lost.

From 10g onwards the buffer size is effectively unlimited but the
default is 2000 characters I think.

Other (better) ways to track progress -

* an AUTONOMOUS TRANSACTION in a logging procedure to log the text with
a date and time for ordering purposes - that way, you keep the text in
order when you SELECT it back. You need to commit in the procedure
though, so it has to be an AUTONOMOUS TRANSACTION to stop it committing
your code.

* DBMS_APPLICATION_INFO has useful utilities to log progress to V$SESSION:

* SET_ACTION
* SET_CLIENT_INFO

are two useful examples. Action goes into the ACTION column and
client_info goes into the CLIENT_INFO column.

There a few "instrumentation" packages out there in Oracle land that you
can use in your code. Ones I know of are ILO from Hotsos/Method R at
http://www.hotsos.com/ilo.html and a new kid on the block, sf_trace from
Steven Feuerstein at www.plsqlchallenge.com<http://www.plsqlchallenge.com>. (library->utilities->scroll
down to near the bottom!)

HTH

--
Cheers,
Norm. [TeamT]