Re: UTL_File

Maxim has provided a nice working example of something you shouldn't do. That is you should not be using dbms_output to buffer data to be printed using utl_file. You should place the data in utl_file to being with.

You can send dbms_output output to a file when the pl/sql is invoked from SQLPlus using the SQLPlus spool command:

set echo off
set serveroutput on size 4096
spool dbmsout
declare
v_x varchar2(20);
begin
for I in 1..10 loop
  v_x := v_x||'X';
  dbms_output.put_line(v_x);
end loop;
end;
/
spool off

Produces a file dbmsout.lst that contains X
XX
XXX
XXXX
XXXXX
XXXXXX
XXXXXXX
XXXXXXXX
XXXXXXXXX
XXXXXXXXXX There are numerous SQLPlus set commands commonly used to control spool file formatting that may be of interest. See SQLPlus manual.

The dbms_output.get_line procedure can also be used from pro* language programs to get messages out of the dbms_output buffer normally displayed on the screen during interactive execution for when the routine is used in batch.

Again in any one process you probably should not be using both dbms_output and utl_file together but would choose one or the other based on what you were doing.

HTH -- Mark D Powell -- Received on Wed Jul 27 2005 - 08:33:13 CDT