Re: utl_file limits?

When was UTL_FILE added to PL/SQL. I have some older versions of Oracle and need to know if they have that capability.

Michael.

Peter Schneider wrote:
>
> >I get an "ORU-10027 buffer overflow, limit of 20,000 bytes" error when
> >writing a long file (it gets to 156 kb) from a procedure. It still does it
> >after opening, writing, flushing, and closing the file on each statement as
> >it loops through creating the line. For example:
> >
> > -- declared earlier...
> > table_dml := UTL_FILE.FOPEN ('c:\aopt\vss\dev\db\install\dml',
> >table_nm||'.sql', 'A');
> > -- Write load data command for table into loaddata.sql
> > UTL_FILE.PUT_LINE(table_dml,col_line);
> > UTL_FILE.FFLUSH(table_dml);
> > UTL_FILE.FCLOSE(table_dml); -- close table DML file
> >
> >I looked high and low for the "buffer" parameter the message is refering to
> >in order to set it higher.
> >
> >Is there a "buffer" parameter for writing out to a file?
> >Is it in the SGA?
> >Is it limited to 20000k or can it be set higher?
> >Any ideas on strategy to get around this limit?
>
> Hi David,
>
> the only utl_file limitations that I'm aware of are that you can only
> write logical lines (i.e. no binary data whatsoever), and that such a
> line may not be longer than 1023 bytes.
>
> I think the ORU-10027 message your are getting is related to some use
> of DBMS_OUTPUT in your code, which has nothing to do with writing to a
> file through utl_file.
>
> Although you can use DBMS_OUTPUT.ENABLE(<buffer_size>) to allocate a
> larger buffer, the maximum value here is 1M, so it's generally a bad
> idea to use DBMS_OUTPUT for an unknown amount of output data.
>
> HTH,
> Peter
>
> --
> Peter Schneider
> peter.schneider_at_okay.net
Received on Wed Aug 12 1998 - 12:42:13 CDT