|
|
Re: PL/SQL UTL_FILE.PUT 1024 buffer limit [message #366153 is a reply to message #365489] |
Fri, 15 December 2000 09:47  |
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;
/
|
|
|