Re: dbms_output in a PL/SQL loop...

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 18 Jan 2003 14:18:13 -0800
Message-ID: <b0cjr501tta@drn.newsguy.com>

In article <3e292f2f$1_at_news.swissonline.ch>, "Gilles says...
>
>Hi,
>I have been trying to put a dbms_outoput.put_line in a PL/SQL Loop to get a
>"real time" print of where the loop actually is working, but it seem that
>all the output records print on the screen only when the loop is finished...
>
>Does anybody know how to get a "real time advance indicator" for a PL/SQL
>loop ?
>
>Thanks in advance for your help...
>
>Gilles
>
>

In addition to dbms_application_info and utl_file -- both of which work great, you can use a pipelined function in 9i:

ops$tkyte_at_ORA920> create or replace type msgType as table of varchar2(80)   2 /

Type created.

ops$tkyte_at_ORA920>
ops$tkyte_at_ORA920> create or replace function foo return msgType   2 PIPELINED
  3 as
  4 begin

  5          pipe row( 'start ' || systimestamp );
  6          for i in 1 .. 10
  7          loop
  8                  pipe row( 'iteration ' || i || ' ' || systimestamp );
  9                  dbms_lock.sleep( 1 );
 10                  pipe row( 'done iteration ' || i );
 11          end loop;
 12          return;

 13 end;
 14 /

Function created.

ops$tkyte_at_ORA920>
ops$tkyte_at_ORA920> set arraysize 1
ops$tkyte_at_ORA920> select * from TABLE(foo);

COLUMN_VALUE

---

start 18-JAN-03 05.17.14.054078000 PM -05:00 iteration 1 18-JAN-03 05.17.14.056678000 PM -05:00 done iteration 1
iteration 2 18-JAN-03 05.17.15.080279000 PM -05:00 done iteration 2
iteration 3 18-JAN-03 05.17.16.103703000 PM -05:00 done iteration 3
iteration 4 18-JAN-03 05.17.17.127135000 PM -05:00 done iteration 4
iteration 5 18-JAN-03 05.17.18.150577000 PM -05:00 done iteration 5
iteration 6 18-JAN-03 05.17.19.174020000 PM -05:00 done iteration 6
iteration 7 18-JAN-03 05.17.20.197475000 PM -05:00 done iteration 7
iteration 8 18-JAN-03 05.17.21.221211000 PM -05:00 done iteration 8
iteration 9 18-JAN-03 05.17.22.244677000 PM -05:00 done iteration 9
iteration 10 18-JAN-03 05.17.23.267685000 PM -05:00 done iteration 10

21 rows selected.

You cannot see it here but the data was fed back to the sqlplus client as it was produced....

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat Jan 18 2003 - 16:18:13 CST