Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL UTL_FILE.PUT 1024 buffer limit
PL/SQL UTL_FILE.PUT 1024 buffer limit [message #365471] Wed, 24 June 1998 14:40 Go to next message
Ravi
Messages: 251
Registered: June 1998
Senior Member
Is there a work around if I need to write 5000 bytes long records thru PL/SQL using UTL_FILE?

It doesn't allow me to write more than 1024 bytes
Re: PL/SQL UTL_FILE.PUT 1024 buffer limit [message #365489 is a reply to message #365471] Wed, 01 July 1998 06:52 Go to previous messageGo to next message
David Calobrisi
Messages: 5
Registered: June 1998
Junior Member
Apparently, this is a limitation in versions prior to 8.0.5. There is no fix for it if you're on an earlier version. I saw this on the Oracle support page where others had asked a similar question.
Re: PL/SQL UTL_FILE.PUT 1024 buffer limit [message #366153 is a reply to message #365489] Fri, 15 December 2000 09:47 Go to previous message
sathya
Messages: 27
Registered: December 2000
Junior Member
Problem Description
-------------------

When using the UTL_FILE package to read from or write to a text file
from PL/SQL, you receive one of the following exceptions:

UTL_FILE.WRITE_ERROR
UTL_FILE.READ_ERROR
User-defined exception

The procedure fails only when attempting to read/write an exceptionally
large record, i.e., greater than 1023 bytes.

Problem Explanation
-------------------

Prior to 8.0.5, the maximum size of an input/output record that could
be passed to UTL_FILE was 1,023 bytes, including the end-of-line
character. With 8.0.5, the limit is now 32,767. In Oracle 8.0.5.1,
1,023 is the default maximum line size.

Solution Description
--------------------

The maximum size of an input/output record is 1,023 bytes; however, you can
specify a larger size in the overloaded version of FOPEN. Add the
MAX_LINESIZE parameter to the UTL_FILE.FOPEN function and specify a value
greater than the record size of the datafile.

The FOPEN procedure has two definitions in the package UTL_FILE for
compatibility reasons. The first definition of FOPEN does not take a
parameter for the maximum line size and uses a default value which is
1,023 on most systems.

The second definition of FOPEN takes a user-specified value for the maximum
line size up to 32,767. This parameter is only available with Oracle 8.0.5
and later versions.

First definition:

FUNCTION fopen(location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2)
RETURN file_type;

Second definition:

FUNCTION fopen(location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
RETURN file_type;

Solution Explanation
--------------------

The MAX_LINESIZE parameter was added to 8.0.5 to get around the
size limitation. This feature is not in the current documentation
for Oracle 8.0.5 PL/SQL. However, it is documented in the "Oracle8i
Supplied Packages Reference, Volume 2."

The following example illustrates how the fopen function is used:

CREATE OR REPLACE PROCEDURE file_test IS

file_handle UTL_FILE.FILE_TYPE; -- file handle of OS flat file
retrieved_buffer VARCHAR2(32767); -- Line retrieved from flat file

BEGIN
-- Open the same file to read from
file_handle :=
UTL_FILE.FOPEN('/u02/home/usupport/lmuddego','myfile.txt','R',32767);

-- Read a line from the file.
UTL_FILE.GET_LINE (file_handle, retrieved_buffer);

-- Print fetched line out to the SQL*PLUS prompt.
DBMS_OUTPUT.PUT_LINE(LENGTH(retrieved_buffer));

-- CLose the file.
UTL_FILE.FCLOSE(file_handle);

file_handle :=
UTL_FILE.FOPEN('/u02/home/usupport/lmuddego','out.txt','W',32767);

UTL_FILE.PUT_LINE (file_handle, retrieved_buffer);

UTL_FILE.FCLOSE(file_handle);

EXCEPTION

WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no_data_found');
UTL_FILE.FCLOSE(file_handle);
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
UTL_FILE.FCLOSE(file_handle);
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE(' UTL_FILE.READ_ERROR');
UTL_FILE.FCLOSE(file_handle);
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
UTL_FILE.FCLOSE(file_handle);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('other stuff');
UTL_FILE.FCLOSE(file_handle);
END;
/
Previous Topic: SOMEBODY ASSIST!!!!! - UTL_FILE package functions fails
Next Topic: composite key
Goto Forum:
  


Current Time: Tue Jun 22 00:12:23 CDT 2021