Oracle Server - UTL

Author

UTL_File

thepauly

2005-07-27, 7:23 am

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?

Maxim Demenko

2005-07-27, 7:23 am

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_a
rray,l_num_lines);

fh:= utl_file. fopen('DATA_DIR','te
stfile.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

Mark D Powell

2005-07-27, 9:23 am

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 --

Maxim Demenko

2005-07-27, 11:23 am

Mark D Powell schrieb:
> 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.

Well, Mark, if you mean , "it should never be designed" - i'm on your
side, if you however mean "it should never be done" - i disagree.
In a well designed program dbms_output usually has no place, as it
serves only debugging purposes. It can have very undesired side effects
if you leave calls to dbms_output in production code. On the other side
calls to utl_file are legitimated in many cases - the 2 packages have
simply different scope of use. In some cases hovewer, it can be
reasonable in the debugging process ( it depends simply on debugging
approach ) to redirect the stderr from terminal to file , which can be
done of course with spool, but can be also done with utl_file. I don't
see any valueable cons/pros for both methods .

Best regards

Maxim

Sponsored Links