Re: 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. (library->utilities->scroll 
down to near the bottom!)


HTH

-- 
Cheers,
Norm. [TeamT]


------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/toad/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/toad/join
    (Yahoo! ID required)

<*> To change settings via email:
    [email protected] 
    [email protected]

<*> To unsubscribe from this group, send an email to:
    [email protected]

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/