Re: dbms_output.get_line(s) how to ?

sahmad_at_mfa.com (Saad Ahmad) wrote:

>Mark Pritchett (mark_at_unisol.demon.co.uk) wrote:
>> Having searched the documentation and drawn a blank with Oracle (UK) I
>> thought I'd see if any one on the Internet could supply an example of
>> how to use the get_line function within PL/SQL.
>> We have a long running Pl/SQL block and wish to display progress prior
>> to its completion. The above function looks ideally suited but all
>> attempts to coerce it into revealing was has been buffered by
>> put_line have failed.
 

>put progress information on a pipe and let the pipe listener
>do the display of the progress.
 

>refer to the dbmspipe package
 

>--
>**************************************************************
>* Saad Ahmad *
>* McHugh Freeman & Associates, Inc. *
>* Ph: (414) 798-8606 Ext. 457 *
>* Fax: (414) 798-8619 *
>* E-Mail: sahmad_at_mfa.com *
>**************************************************************

Calls to put_line are NO-OPS unless you have called dbms_output.enable (or issue set serveroutput on in SQL*Plus/DBA).

You won't be able to show progress prior to the completion of the pl/sql block using dbms_output. You need TWO connections to show status and the buffered dbms_output information is only available to the one session, the one blocked waiting for the pl/sql block to complete.

You can use pipes as above but you will need TWO connections to the data from each client. One connection invokes the stored procedure and the other reads from the pipe the procedure is writing to.

Another viable alternative you might want to look into is dbms_jobs. You can queue any sql or pl/sql statment to be executed in the background. Your procedure can then periodically write to a pipe or put information in a status table (commiting after it does so) and then read the piped/table data out periodically in your client application. You would only need one connection using this method.

(documentation for dbms_jobs prior to 7.2 documentation is found in $ORACLE_HOME/rdbms/admin/dbmsjob.sql)

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Wed Oct 04 1995 - 00:00:00 CET