UTL_FILE , 2 of 2

Summary of Subprograms

Table 60-2 UTL_FILE Subprograms

Subprogram	Description
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;

Parameters

Table 60-3 FOPEN Function Parameters

Parameters Description

Parameters	Description
location	Operating system-specific string that specifies the directory in which to open the file.
filename	Name of the file, including extension (file type), without any directory path information. (Under the UNIX operating system, the filename cannot be terminated with a '/'.).
open_mode	String that specifies how the fie is to be opened (either upper or lower case letters can be used). The supported values, and the `UTL_FILE` procedures that can be used with them are: '`r`' read text (`GET_LINE`) '`w`' write text (`PUT`, `PUT_LINE`, `NEW_LINE`, `PUTF`, `FFLUSH`) '`a`' append text (`PUT`, `PUT_LINE`, `NEW_LINE`, `PUTF`, `FFLUSH`)

location

Operating system-specific string that specifies the directory in which to open the file.

filename

Name of the file, including extension (file type), without any directory path information. (Under the UNIX operating system, the filename cannot be terminated with a '/'.).

open_mode

String that specifies how the fie is to be opened (either upper or lower case letters can be used).

The supported values, and the UTL_FILE procedures that can be used with them are:

'r' read text (GET_LINE)

'w' write text (PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH)

'a' append text (PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH)

Note:
If you open a file that does not exist using the 'a' value for open_mode, then the file is created in write ('w') mode.

Returns

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.

Note:
The file location and file name parameters are supplied to the FOPEN function as separate strings, so that the file location can be checked against the list of accessible directories as specified in the initialization file. Together, the file location and name must represent a legal filename on the system, and the directory must be accessible. A subdirectory of an accessible directory is not necessarily also accessible; it too must be specified using a complete path name in the initialization file.
Operating system-specific parameters, such as C-shell environment variables under UNIX, cannot be used in the file location or file name parameters.

Exceptions

INVALID_PATH
INVALID_MODE
INVALID_OPERATION

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;

Parameters

Table 60-4 IS_OPEN Function Parameters

Parameter	Description
file	Active file handle returned by an `FOPEN` call.

Returns

TRUE or FALSE

Exceptions

None.

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);

Parameters

Table 60-5 FCLOSE Procedure Parameters

Parameter	Description
file	Active file handle returned by an `FOPEN` call.

Exceptions

WRITE_ERROR
INVALID_FILEHANDLE

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.

Note:
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.

Syntax

UTL_FILE.FCLOSE_ALL;

Parameters

None.

Exceptions

WRITE_ERROR

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);

Parameters

Table 60-6 GET_LINE Procedure Parameters

Parameters Description

Parameters	Description
file	Active file handle returned by an `FOPEN` call. The file must be open for reading (mode '`r`'), otherwise an `INVALID_OPERATION` exception is raised.
buffer	Data buffer to receive the line read from the file.

file

Active file handle returned by an FOPEN call.

The file must be open for reading (mode 'r'), otherwise an INVALID_OPERATION exception is raised.

buffer

Data buffer to receive the line read from the file.

Exceptions

INVALID_FILEHANDLE
INVALID_OPERATION
READ_ERROR
NO_DATA_FOUND
VALUE_ERROR

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);

Parameters

Table 60-7 PUT Procedure Parameters

Parameters Description

file

Active file handle returned by an FOPEN call.

buffer

Buffer that contains the text to be written to the file.

You must have opened the file using mode 'w' or mode 'a'; otherwise, an INVALID_OPERATION exception is raised.

Exceptions

INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

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);

Parameters

Table 60-8 NEW_LINE Procedure Parameters

Parameters	Description
file	Active file handle returned by an `FOPEN` call.
lines	Number of line terminators to be written to the file.

Exceptions

INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

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);

Parameters

Table 60-9 PUT_LINE Procedure Parameters

Parameters	Description
file	Active file handle returned by an `FOPEN` call.
buffer	Text buffer that contains the lines to be written to the file.

Exceptions

INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

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]);

Parameters

Table 60-10 PUTF Procedure Parameters

Parameters Description

file

Active file handle returned by an FOPEN call.

format

Format string that can contain text as well as the formatting characters '\n' and '%s'.

arg1..arg5

From one to five operational argument strings.

Argument strings are substituted, in order, for the '%s' formatters in the format string.

If there are more formatters in the format parameter string than there are arguments, then an empty string is substituted for each '%s' for which there is no argument.

Example

The following example writes the lines:

Hello, world!
I come from Zork with greetings for all earthlings.

my_world  varchar2(4) := 'Zork';
...
PUTF(my_handle, 'Hello, world!\nI come from %s with %s.\n',
                my_world,
                'greetings for all earthlings');

If there are more %s formatters in the format parameter than there are arguments, then an empty string is substituted for each %s for which there is no matching argument.

Exceptions

INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

FFLUSH Procedure

FFLUSH physically writes pending data to the file identified by the file handle. 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.

Syntax

UTL_FILE.FFLUSH (
   file  IN FILE_TYPE);
invalid_maxlinesize  EXCEPTION;

Parameters

Table 60-11 FFLUSH Procedure Parameters

Parameters	Description
file	Active file handle returned by an `FOPEN` call.

Exceptions

INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

FOPEN Function

This function opens a file. You can have a maximum of 50 files open simultaneously.

Note:
This version of FOPEN enables you to specify the desired maximum line size. The other version of the "FOPEN Function" uses the default line size.

Syntax

UTL_FILE.FOPEN (
   location     IN VARCHAR2,
   filename     IN VARCHAR2,
   open_mode    IN VARCHAR2,
   max_linesize IN BINARY_INTEGER) 
  RETURN file_type;

Parameters

Table 60-12 FOPEN Function Parameters

Parameter	Description
location	Directory location of file.
filename	File name (including extension).
open_mode	Open mode ('`r`', '`w`', '`a`').
max_linesize	Maximum number of characters per line, including the newline character, for this file. (minimum value 1, maximum value 32767).

Returns

Table 60-13 FOPEN Function Returns

Return	Description
file_type	Handle to open file.

Exceptions

INVALID_PATH: File location or name was invalid.
INVALID_MODE: The open_mode string was invalid.
INVALID_OPERATION: File could not be opened as requested.
INVALID_MAXLINESIZE: Specified max_linesize is too large or too small.

%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.