Re: Autonomous Transactions and DBMS_OUTPUT

A copy of this was sent to jason_at_seahorse.demon.co.uk (Jason Salter) (if that email address didn't require changing) On Tue, 10 Aug 1999 15:57:57 GMT, you wrote:

>Anyone know if you can use autonomous transactions (Oracle 8) to
>display output from DBMS_OUTPUT.PUT_LINE as and when it happens
>instead of having to wait until a procedure ends?
>
>Regards,
>Jason.

No you cannot.

dbms_output is always pulled by the client (eg: sqlplus makes a call to dbms_output.get_lines AFTER your procedure returns control to it -- it being sqlplus). The outer most procedure must complete before dbms_output stuff can ever been seen. autonomous procedures let you create child transactions -- they do not return control to the caller.

utl_file (write to an OS file) with a "tail -f filename" works pretty well to monitor progress..

if you are just trying to monitor the progress (or lack thereof) of a long running procedure, you can use dbms_application_info calls in the procedure to update values in the v$session table (and v$session_longops in 8.0 and up) and query these tables from another session..

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Aug 10 1999 - 15:00:21 CDT