Oracle Display the process of the procedure ( oracle pipe , dbms_output , prompt )

From : asktom

[@more@]

It is because prior to 9i -- a stored procedure call would goto the database, run the procedure 
from start to finish and then return to the client.  The client has no ability to get results from 
the stored procedure until its totally done. It is one message over, one message back.  The stored 
procedure runs in the server, not on the client so the stored procedure cannot really do any I/O on 
the client screen (and think of all of the interfaces - gui's, command line, etc -- there would be 
no way for the stored proc to do I/O to the screen)

Now, in 9i, we can actually get interactive results from a stored procedure using pipe lining (a 
cool feature that wasn't really designed to do exactly what I'm about to do -- rather this is a 
nice side effect).  Consider this small example:

ops$tkyte@ORA9I.WORLD> create or replace type messageTabType
  2  as table of varchar2(4000)
  3  /

Type created.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> create or replace function my_stored_proc( p_inputs in varchar2 )
  2  return messageTabType
  3  PIPELINED
  4  as
  5  begin
  6      for i in 1 .. 10
  7      loop
  8          pipe row( 'processing iteration      ' ||
  9                      i || ' ' || systimestamp );
 10          dbms_lock.sleep(1);
 11          pipe row( 'done processing iteration ' ||
 12                      i || ' ' || systimestamp );
 13      end loop;
 14      pipe row( 'All done, success' );
 15  
 16      return;
 17  end;
 18  /

Function created.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> set arraysize 1
ops$tkyte@ORA9I.WORLD> select * from table( my_stored_proc( 'foobar' ) );

COLUMN_VALUE
--------------------------------------------------------------------------------
processing iteration      1 16-NOV-01 08.14.38.335479000 AM -05:00
done processing iteration 1 16-NOV-01 08.14.39.360806000 AM -05:00
processing iteration      2 16-NOV-01 08.14.39.361372000 AM -05:00
done processing iteration 2 16-NOV-01 08.14.40.390780000 AM -05:00
processing iteration      3 16-NOV-01 08.14.40.391328000 AM -05:00
done processing iteration 3 16-NOV-01 08.14.41.420795000 AM -05:00
processing iteration      4 16-NOV-01 08.14.41.421341000 AM -05:00
done processing iteration 4 16-NOV-01 08.14.42.450819000 AM -05:00
processing iteration      5 16-NOV-01 08.14.42.451383000 AM -05:00
done processing iteration 5 16-NOV-01 08.14.43.480794000 AM -05:00
processing iteration      6 16-NOV-01 08.14.43.481343000 AM -05:00
done processing iteration 6 16-NOV-01 08.14.44.511147000 AM -05:00
processing iteration      7 16-NOV-01 08.14.44.511987000 AM -05:00
done processing iteration 7 16-NOV-01 08.14.45.540863000 AM -05:00
processing iteration      8 16-NOV-01 08.14.45.541468000 AM -05:00
done processing iteration 8 16-NOV-01 08.14.46.571104000 AM -05:00
processing iteration      9 16-NOV-01 08.14.46.571923000 AM -05:00
done processing iteration 9 16-NOV-01 08.14.47.600860000 AM -05:00
processing iteration      10 16-NOV-01 08.14.47.601463000 AM -05:00
done processing iteration 10 16-NOV-01 08.14.48.630873000 AM -05:00
All done, success

21 rows selected.

It is not obvious here but -- each pair of outputs from the procedure appeared in sqlplus -- in 
real time -- every second (setting the arraysize was important here, else plus would have waited 
for 15 rows to be generated).  

So, in 9i, if you want "real time" output from the procedure, you would simply use PIPE ROW instead 
of dbms_output and you would "select * from your_procedure" instead of execute your_procedure.

If your procedure modifies the database, you would have to code it as an autonomous transaction:

ops$tkyte@ORA9I.WORLD> create or replace function my_stored_proc( p_inputs in varchar2 )
  2  return messageTabType
  3  PIPELINED
  4  as
  5          pragma autonomous_transaction;
  6  begin
  7      for i in 1 .. 10
  8      loop
  9          pipe row( 'processing iteration      ' ||
 10                      i || ' ' || systimestamp );
 11          dbms_lock.sleep(1);
 12          insert into t values ( i );
 13          pipe row( 'done processing iteration ' ||
 14                      i || ' ' || systimestamp );
 15      end loop;
 16      pipe row( 'All done, success' );
 17      commit;
 18      return;
 19  end;
 20  /

Function created.

to avoid:

ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "OPS$TKYTE.MY_STORED_PROC", line 11
ORA-06512: at line 1

but otherwise it will work just fine.