UTL FILE PKG

UTL_FILE
The UTL_FILE package lets your PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file input/output (I/O).
The file I/O capabilities are similar to those of the standard operating system stream file I/O (OPEN, GET, PUT, CLOSE), with some limitations. For example, call the FOPEN function to return a file handle, which you then use in subsequent calls to GET_LINE or PUT to perform stream I/O to a file. When you are done performing I/O on the file, call FCLOSE to complete any output and to free any resources associated with the file.
Server Security
Server security for PL/SQL file I/O consists of a restriction on the directories that can be accessed. Accessible directories must be specified in the instance parameter initialization file (INIT.ORA).
Specify the accessible directories for the UTL_FILE functions in the initialization file using the UTL_FILE_DIR parameter. For example:
UTL_FILE_DIR =
Examples (UNIX-Specific)
If the parameter initialization file contains only:
UTL_FILE_DIR=/appl/gl/log
UTL_FILE_DIR=/appl/gl/out

Then, the following file locations and filenames are valid:
FILE LOCATION FILENAME
/appl/gl/log L10324.log
/appl/gl/out O10324.out
Types
TYPE file_type IS RECORD (id BINARY_INTEGER);
Summary of Subprograms
FOPEN function
Opens a file for input or output with the default line size.
IS_OPEN function
Determines if a file handle refers to an open file.
FCLOSE procedure
Closes a file.
FCLOSE_ALL procedure
Closes all open file handles.
GET_LINE procedure
Reads a line of text from an open file.
PUT procedure
Writes a line to a file. This does not append a line terminator.
NEW_LINE procedure
Writes one or more OS-specific line terminators to a file.
PUT_LINE procedure
Writes a line to a file. This appends an OS-specific line terminator.
PUTF procedure
A PUT procedure with formatting.
FFLUSH procedure
Physically writes all pending output to a file.
FOPEN function
Opens a file with the maximum line size specified.
FOPEN function
This function opens a file for input or output. The file location must be an accessible directory, as defined in the instance's initialization parameter UTL_FILE_DIR. The complete directory path must already exist; it is not created by FOPEN.
FOPEN returns a file handle, which must be used in all subsequent I/O operations on the file.
This version of FOPEN does not take a parameter for the maximum line size. Thus, the default (which is 1023 on most systems) is used. To specify a different maximum line size, use the other, overloaded version of "FOPEN function".
You can have a maximum of 50 files open simultaneously.
Syntax
UTL_FILE.FOPEN (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2)
RETURN UTL_FILE.FILE_TYPE;
IS_OPEN function
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.
Syntax
UTL_FILE.IS_OPEN (
file IN FILE_TYPE)
RETURN BOOLEAN;
FCLOSE procedure
This procedure closes an open file identified by a file handle. If there is buffered data yet to be written when FCLOSE runs, then you may receive a WRITE_ERROR exception when closing a file.
Syntax
UTL_FILE.FCLOSE (
file IN OUT FILE_TYPE);
FCLOSE_ALL procedure
This procedure 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.
Syntax
UTL_FILE.FCLOSE_ALL;
GET_LINE procedure
This procedure reads a line of text from the 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.
If the line does not fit in the buffer, then a VALUE_ERROR exception is raised. If no text was read due to "end of file," then the NO_DATA_FOUND exception is raised.
Because the line terminator character is not read into the buffer, reading blank lines returns empty strings.
The maximum size of an input record is 1023 bytes, unless you specify a larger size in the overloaded version of FOPEN.
Syntax
UTL_FILE.GET_LINE (
file IN FILE_TYPE,
buffer OUT VARCHAR2);
PUT procedure
PUT 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. 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.
The maximum size of an input record is 1023 bytes, unless you specify a larger size in the overloaded version of FOPEN.
Syntax
UTL_FILE.PUT (
file IN FILE_TYPE,
buffer IN VARCHAR2);
NEW_LINE procedure
This procedure writes one or more line terminators to the file identified by the input file handle. This procedure is separate from PUT because the line terminator is a platform-specific character or sequence of characters.
Syntax
UTL_FILE.NEW_LINE (
file IN FILE_TYPE,
lines IN NATURAL := 1);
PUT_LINE procedure
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.
The maximum size for an output record is 1023 bytes, unless you specify a larger value using the overloaded version of FOPEN.
Syntax
UTL_FILE.PUT_LINE (
file IN FILE_TYPE,
buffer IN VARCHAR2);
PUTF procedure
This procedure is a formatted PUT procedure. It works like a limited printf(). 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
Syntax
UTL_FILE.PUTF (
file IN FILE_TYPE,
format IN VARCHAR2,
[arg1 IN VARCHAR2 DEFAULT NULL,
. . .
arg5 IN VARCHAR2 DEFAULT NULL]);
FFLUSH procedure
FFLUSH physically writes all pending data to the file identified by the file handle. Normally, data being written to a file is buffered. The FFLUSH procedure forces any buffered data to be written to the file.
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.
Syntax
UTL_FILE.FFLUSH (
file IN FILE_TYPE);
invalid_maxlinesize EXCEPTION;
Exceptions

INVALID_PATH
File location or filename was invalid.
INVALID_MODE
The open_mode parameter in FOPEN was invalid.
INVALID_FILEHANDLE
File handle was invalid.
INVALID_OPERATION
File could not be opened or operated on as requested.
READ_ERROR
Operating system error occurred during the read operation.
WRITE_ERROR
Operating system error occurred during the write operation.
INTERNAL_ERROR