Re: UTL_File

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Wed, 27 Jul 2005 12:18:59 +0200
Message-ID: <42e7609e$0$11758$9b4e6d93@newsread4.arcor-online.net>

thepauly schrieb:
> Can anyone tell me if and how you can write what is in the DBMS_OUTPUT
> dirctly to a file using the UTL_FILE using Oracle 9i?
> I have the UTL_FILE set up and working but can't stream information
> directly from DBMS_OUTPUT to a file and I was wondering if this is
> possible?
>

The content of buffer is in plsql table, which is not intended for public access, so you can not directly access that table. But you have an API to this data structure ( with dbms_output.get_line and dbms_output.get_lines ), so you can retrieve and write it out with utl_file.

CREATE DIRECTORY DATA_DIR AS '/tmp'
/

DECLARE
fh utl_file.file_type;

l_string VARCHAR2(255);
l_string_array dbms_output.chararr;
l_num_lines PLS_INTEGER := 25;

BEGIN
l_string := 'Hello';
-- populate buffer
FOR i IN 1..25 LOOP
dbms_output.put_line(l_string);
END LOOP;
-- get the content of buffer into appropriate collection dbms_output.get_lines(l_string_array,l_num_lines);

fh:= utl_file.fopen('DATA_DIR','testfile.dat','W'); FOR i IN 1..l_num_lines LOOP
NULL;
-- write content of collection to file
utl_file.put_line(fh,l_string_array(i)); END LOOP;
utl_file.fflush(fh);
utl_file.fclose(fh);
END;
/

Best regards

Maxim Received on Wed Jul 27 2005 - 05:18:59 CDT