UTL_FILE

With UTL_FILE package, PL/SQL programs can Read and Write operating system text files. It provides a restricted version of OS stream file I/O. Procedures in UTL_FILE can also raise predefined PL/SQL exceptions such as NO_DATA_FOUND / VALUE_ERROR.

UTL_FILE Package Exceptions	Description
INVALID_PATH	File location is invalid.
INVALID_MODE	open_mode parameter in FOPEN is invalid.
INVALID_FILEHANDLE	File handle is invalid.
INVALID_OPERATION	File could not be opened or operated on as requested.
READ_ERROR	Destination buffer too small or Operating System error occurred during the read operation.
WRITE_ERROR	Operating system error occurred during the write operation.
INTERNAL_ERROR	Unspecified PL/SQL error
CHARSETMISMATCH	File is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE.
FILE_OPEN	Requested operation failed because the file is open.
INVALID_MAXLINESIZE	MAX_LINESIZE value for FOPEN( ) is invalid; it should be within the range 1 to 32767.
INVALID_FILENAME	The filename parameter is invalid.
ACCESS_DENIED	Permission to access to the file location is denied.
INVALID_OFFSET	Causes of the INVALID_OFFSET exception: ABSOLUTE_OFFSET = NULL and RELATIVE_OFFSET = NULL, or ABSOLUTE_OFFSET < 0, or Either offset caused a seek past the end of the file
DELETE_FAILED	Requested file delete operation failed
RENAME_FAILED	Requested file rename operation failed

UTL_FILE Subprograms	Description
FCLOSE	Closes a file
FCLOSE_ALL	Closes all open file handles
FCOPY	Copies a contiguous portion of a file to a newly created file
FFLUSH	Physically writes all pending output to a file
FGETATTR	Reads and returns the attributes of a disk file
FGETPOS (fn)	Returns the current relative offset position within a file, in bytes
FOPEN (fn)	Opens a file for input or output
FOPEN_NCHAR (fn)	Opens a file in Unicode for input or output
FREMOVE	Deletes a disk file, assuming that you have sufficient privileges
FRENAME	Renames an existing file to a new name, similar to the UNIX mv function
FSEEK	Adjusts the file pointer forward or backward within the file by the number of bytes specified
GET_LINE	Reads text from an open file
GET_LINE_NCHAR	Reads text in Unicode from an open file
GET_RAW	Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read
IS_OPEN (fn)	Determines if a file handle refers to an open file
NEW_LINE	Writes one or more operating system-specific line terminators to a file
PUT	Writes a string to a file
PUT_LINE	Writes a line to a file, and so appends an operating system-specific line terminator
PUT_LINE_NCHAR	Writes a Unicode line to a file
PUT_NCHAR	Writes a Unicode string to a file
PUTF	PUT procedure with formatting
PUTF_NCHAR	PUT_NCHAR procedure with formatting, & writes a Unicode string to a file, with formatting
PUT_RAW	Accepts as input a RAW data value and writes the value to the output buffer

UTIL_FILE Subprograms	Description	Syntax	Notes
FCLOSE	Closes an open file identified by a file handle Exceptions: WRITE_ERROR INVALID_FILEHANDLE	UTL_FILE.FCLOSE ( file IN OUT FILE_TYPE);	·         If there is buffered data yet to be written when FCLOSE runs, then you may receive a WRITE_ERROR exception when closing a file.
FCLOSE_ALL	Closes all open file handles for the session. This should be used as an emergency cleanup procedure, for example, when a PL/SQL program exits on an exception. Exceptions: WRITE_ERROR	UTL_FILE.FCLOSE_ALL;	·         FCLOSE_ALL does not alter the state of the open file handles held by the user. ·         This means that an IS_OPEN test on a file handle after an FCLOSE_ALL call still returns TRUE, even though the file has been closed. No further read or write operations can be performed on a file that was open before an FCLOSE_ALL.
FCOPY	Copies a contiguous portion of a file to a newly created file Exceptions: INVALID_FILENAME INVALID_PATH INVALID_OPERATION INVALID_OFFSET READ_ERROR WRITE_ERROR	UTL_FILE.FCOPY ( src_location IN VARCHAR2, src_filename IN VARCHAR2, dest_location IN VARCHAR2, dest_filename IN VARCHAR2, start_line IN BINARY_INTEGER DEFAULT 1, end_line IN BINARY_INTEGER DEFAULT NULL);	·         By default, the whole file is copied if the start_line and end_line parameters are omitted. The source file is opened in read mode. ·         The destination file is opened in write mode. ·         Starting and ending line number can optionally be specified to select a portion from the center of the source file for copying.
FFLUSH	Physically writes all pending output to a file Exceptions: INVALID_FILENAME INVALID_MAXLINESIZE INVALID_OPERATION WRITE_ERROR	UTL_FILE.FFLUSH ( file IN FILE_TYPE);	·         Normally, data being written to a file is buffered. The FFLUSH procedure forces the buffered data to be written to the file. The data must be terminated with a newline character. ·         Flushing is useful when the file must be read while still open. For example, debugging messages can be flushed to the file so that they can be read immediately.
FGETATTR	Reads & returns the attributes of a disk file Exceptions: INVALID_PATH INVALID_FILENAME INVALID_OPERATION READ_ERROR ACCESS_DENIED	UTL_FILE.FGETATTR( location IN VARCHAR2, filename IN VARCHAR2, fexists OUT BOOLEAN, file_length OUT NUMBER, block_size OUT BINARY_INTEGER);
FGETPOS (fn)	Returns the current relative offset position within a file, in bytes Exceptions:  INVALID_FILEHANDLE INVALID_OPERATION READ_ERROR	UTL_FILE.FGETPOS ( file IN FILE_TYPE) RETURN PLS_INTEGER;	■ If file is opened for byte mode operations, and then the INVALID OPERATION exception is raised. ■ Return Values: FGETPOS returns the relative offset position for an open file, in bytes. It raises an exception if the file is not open. It returns 0 for the beginning of the file.
FOPEN (fn)	Opens a file for input or output. You can specify the maximum line size and have a maximum of 50 files open simultaneously Exceptions: INVALID_MAXILINESIZE INVALID_MODE INVALID_OPERATION INVALID_PATH	UTL_FILE.FOPEN ( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER DEFAULT 1024) RETURN FILE_TYPE;	·         The file location and file name parameters must be supplied to the FOPEN function as quoted strings so that the file location can be checked against the list of accessible directories as specified by the ALL_DIRECTORIES view of accessible directory objects. ·         Return Values: FOPEN returns a file handle, which must be passed to all subsequent procedures that operate on that file. The specific contents of the file handle are private to the UTL_FILE package, and individual components should not be referenced or changed by the UTL_FILE user. o    It returns FILE_TYPE -- Handle to open file
FOPEN_NCHAR (fn)	Opens a file in national character set mode (Unicode) for input or output, with the maximum line size specified. You can have a maximum of 50 files open simultaneously. With this function, you can read or write a text file in Unicode instead of in the DB character set. Exceptions: INVALID_MAXILINESIZE INVALID_MODE INVALID_OPERATION INVALID_PATH	UTL_FILE.FOPEN_NCHAR ( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER DEFAULT 1024) RETURN FILE_TYPE;	·         Even though the contents of an NVARCHAR2 buffer may be AL16UTF16 or UTF8 (depending on the national character set of the DB), the contents of the file are always read and written in UTF8. UTL_FILE converts between UTF8 and AL16UTF16 as necessary. ·         Return Values: FOPEN_NCHAR returns a file handle, which must be passed to all subsequent procedures that operate on that file. The specific contents of the file handle are private to the UTL_FILE package, and individual components should not be referenced or changed by the UTL_FILE user. o    It returns FILE_TYPE -- Handle to open file
FREMOVE	Deletes a disk file, assuming that you have sufficient privileges Exceptions: ACCESS_DENIED DELETE_FAILED INVALID_FILENAME INVALID_OPERATION INVALID_PATH	UTL_FILE.FREMOVE ( location IN VARCHAR2, filename IN VARCHAR2);	·         FREMOVE does not verify privileges before deleting a file. The O/S verifies file and directory permissions. An exception is returned on failure.
FRENAME	Renames an existing file to a new name, similar to the UNIX mv function Exceptions: ACCESS_DENIED INVALID_FILENAME INVALID_PATH RENAME_FAILED	UTL_FILE.FRENAME ( src_location IN VARCHAR2, src_filename IN VARCHAR2, dest_location IN VARCHAR2, dest_filename IN VARCHAR2, overwrite IN BOOLEAN DEFAULT FALSE);
FSEEK	Adjusts the file pointer forward / backward within the file by the number of bytes specified Exceptions: INVALID_FILEHANDLE INVALID_OFFSET INVALID_OPERATION READ_ERROR	UTL_FILE.FSEEK ( file IN OUT UTL_FILE.FILE_TYPE, absolute_offset IN PL_INTEGER DEFAULT NULL, relative_offset IN PLS_INTEGER DEFAULT NULL);	·         Using FSEEK, you can read previous lines in the file without first closing & reopening the file. You must know the number of bytes by which you want to navigate. ·         If relative_offset, the procedure seeks forward. If relative_offset > 0, or backward, if relative_offset < 0, the procedure seeks through the file by the number of relative_offset bytes specified. ·          If the beginning of the file is reached before the number of bytes specified, and then the file pointer is placed at the beginning of the file. If the end of the file is reached before the number of bytes specified, then an INVALID_OFFSET error is raised. ·         If absolute_offset, the procedure seeks to an absolute location specified in bytes. ·         If file is opened for byte mode operations, then the INVALID OPERATION exception is raised.
GET_LINE	■ Reads text from an open file identified by the file handle and places the text in the output buffer parameter. ■ Text is read up to, but not including, the line terminator, or up to the end of the file, or up to the end of the len parameter. It cannot exceed the max_linesize specified in FOPEN. Exceptions: INVALID_FILEHANDLE INVALID_OPERATION NO_DATA_FOUND READ_ERROR	UTL_FILE.GET_LINE ( file IN FILE_TYPE, buffer OUT VARCHAR2, len IN PLS_INTEGER DEFAULT NULL);	■ If the line does not fit in the buffer, a READ_ERROR exception is raised. If no text was read due to end of file, the NO_DATA_FOUND exception is raised. If the file is opened for byte mode operations, the INVALID_OPERATION exception is raised. ■ Because the line terminator character is not read into the buffer, reading blank lines returns empty strings. ■ Maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. If unspecified, Oracle supplies a default value of 1024.
GET_LINE_NCHAR	Reads text in Unicode from an open file Exceptions: INVALID_FILEHANDLE INVALID_OPERATION NO_DATA_FOUND READ_ERROR	UTL_FILE.GET_LINE_NCHAR ( file IN FILE_TYPE, buffer OUT NVARCHAR2, len IN PLS_INTEGER DEFAULT NULL);	■ This procedure reads text from the open file identified by the file handle and places the text in the output buffer parameter. With this function, you can read a text file in Unicode instead of in the DB character set. The file must be opened in national character set mode, and must be encoded in the UTF8 character set. ■ The expected buffer datatype is NVARCHAR2. If a variable of another datatype, such as NCHAR, NCLOB, or VARCHAR2 is specified, PL/SQL will perform standard implicit conversion from NVARCHAR2 after the text is read.
GET_RAW	Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read. UTL_FILE.GET_RAW ignores line terminators. Exceptions: INVALID_FILEHANDLE INVALID_OPERATION LENGTH_MISMATCH NO_DATA_FOUND READ_ERROR	UTL_FILE.GET_RAW ( file IN UTL_FILE.FILE_TYPE, buffer OUT NOCOPY RAW, len IN PLS_INTEGER DEFAULT NULL);	■ The subprogram will raise No_Data_Found when it attempts to read past the end of the file. Your application should allow for this by catching the exception in its processing loop. PROCEDURE Sys.p (n IN VARCHAR2) IS h UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN('D', n, 'r', 32767); Buf RAW(32767); Amnt CONSTANT PLS_INTEGER := 32767; BEGIN LOOP BEGIN Utl_File.Get_Raw(h, Buf, Amnt); -- Do something with this chunk EXCEPTION WHEN No_Data_Found THEN EXIT; END; END LOOP; UTL_FILE.FCLOSE (h); END;
IS_OPEN (fn)	Determines if a file handle refers to an open file Exceptions: INVALID_FILEHANDLE	UTL_FILE.IS_OPEN ( file IN FILE_TYPE) RETURN BOOLEAN;	■ This function tests a file handle to see if it identifies an open file. IS_OPEN reports only whether a file handle represents a file that has been opened, but not yet closed. It does not guarantee that there will be no operating system errors when you attempt to use the file handle. ■ Return Values: TRUE or FALSE
NEW_LINE	Writes one or more operating system-specific line terminators to a file identified by the input file handle.	UTL_FILE.NEW_LINE ( file IN FILE_TYPE, lines IN BINARY_INTEGER := 1);	This procedure is separate from PUT because the line terminator is a platform-specific character or sequence of characters.
PUT	Writes a String to a file (i.e., writes the text string stored in the buffer parameter to the open file identified by the file handle.) Exceptions: INVALID_FILEHANDLE INVALID_OPERATION WRITE_ERROR	UTL_FILE.PUT ( file IN FILE_TYPE, buffer IN VARCHAR2);	■ The file must be open for write operations. No line terminator is appended by PUT; use NEW_LINE to terminate the line or use PUT_LINE to write a complete line with a line terminator. ■ Maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. If unspecified, Oracle supplies a default value of 1024. The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes. ■ If file is opened for byte mode operations, and then the INVALID OPERATION exception is raised.
PUT_LINE	Writes a line to a file, and so appends an operating system-specific line terminator Exceptions: INVALID_FILEHANDLE INVALID_OPERATION WRITE_ERROR	UTL_FILE.PUT_LINE ( file IN FILE_TYPE, buffer IN VARCHAR2, autoflush IN BOOLEAN DEFAULT FALSE);	■ This procedure writes the text string stored in the buffer parameter to the open file identified by the file handle. The file must be open for write operations. ■ PUT_LINE terminates the line with the platform-specific line terminator character or characters.
PUT_LINE_NCHAR	Writes a Unicode line to a file Exceptions: INVALID_FILEHANDLE INVALID_OPERATION WRITE_ERROR	UTL_FILE.PUT_LINE_NCHAR ( file IN FILE_TYPE, buffer IN NVARCHAR2);	■ This procedure writes the text string stored in the buffer parameter to the open file identified by the file handle. With this function, you can write a text file in Unicode instead of in the DB character set. This procedure is equivalent to the PUT_NCHAR, except that the line separator is appended to the written text.
PUT_NCHAR	Writes a Unicode string to a file Exceptions: INVALID_FILEHANDLE INVALID_OPERATION WRITE_ERROR	UTL_FILE.PUT_NCHAR ( file IN FILE_TYPE, buffer IN NVARCHAR2);	■ This procedure writes the text string stored in the buffer parameter to the open file identified by the file handle. ■ With this function, you can write a text file in Unicode instead of in the DB character set. The file must be opened in the national character set mode. The text string will be written in the UTF8 character set. ■ The expected buffer datatype is NVARCHAR2. If a variable of another datatype is specified, PL/SQL will perform implicit conversion to NVARCHAR2 before writing the text.
PUTF	PUT procedure with formatting Exceptions: INVALID_FILEHANDLE INVALID_OPERATION WRITE_ERROR	UTL_FILE.PUTF ( file IN FILE_TYPE, format IN VARCHAR2, [arg1 IN VARCHAR2 DEFAULT NULL, . . . arg5 IN VARCHAR2 DEFAULT NULL]);	■ This procedure is a formatted PUT procedure. It works like a limited printf( ). ■ If file is opened for byte mode operations, then the INVALID OPERATION exception is raised. ■ The format string can contain any text, but the character sequences %s and \n have special meaning. %s -- Substitute this sequence with the string value of the next argument in the argument list. \n -- Substitute with the appropriate platform-specific line terminator.
PUTF_NCHAR	PUT_NCHAR procedure with formatting, & writes a Unicode string to a file, with formatting Exceptions: INVALID_FILEHANDLE INVALID_OPERATION WRITE_ERROR	UTL_FILE.PUTF_NCHAR ( file IN FILE_TYPE, format IN NVARCHAR2, [arg1 IN NVARCHAR2 DEFAULT NULL, . . . arg5 IN NVARCHAR2 DEFAULT NULL]);	■ This procedure is a formatted version of a PUT_NCHAR Procedure. Using PUTF_NCHAR, you can write a text file in Unicode instead of in the DB character set. It accepts a format string with formatting elements \n and %s, and up to five arguments to be substituted for consecutive instances of %s in the format string. The expected datatype of the format string and the arguments is NVARCHAR2. ■ If variables of another DB are specified, PL/SQL will perform implicit conversion to NVARCHAR2 before formatting the text. Formatted text is written in the UTF8 character set to the file identified by the file handle. The file must be opened in the national character set mode.
PUT_RAW	Accepts as input a RAW data value and writes the value to the output buffer Exceptions: INVALID_FILEHANDLE INVALID_OPERATION WRITE_ERROR	UTL_FILE.PUT_RAW ( file IN UTL_FILE.FILE_TYPE, buffer IN RAW, autoflush IN BOOLEAN DEFAULT FALSE);	■ You can request an automatic flush of the buffer by setting the third argument to TRUE.