Oracle7 Server Application Developer's Guide

PL/SQL File I/O

The release 7.3 Oracle Server adds file input/output capabilities to PL/SQL. This is done through the supplied package UTL_FILE.

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, you 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, you call FCLOSE to complete any output and to free any resources associated with the file.

Summary

Table 8 - 13 summarizes the procedures you can call in the UTL_FILE package.

Function/Procedure Description Refer to
FOPEN Open a file for input or ouput. Create an output file if it does not exist.
IS_OPEN Determine if a file handle refers to an open file.
FCLOSE Close a file.
FCLOSE_ALL Close all open file handles.
GET_LINE Read a line of text from an open file.
PUT Write a line to a file. Do not append a line terminator.
PUT_LINE Write a line to a file. Append an OS-specific line terminator.
PUTF A PUT procedure with formatting.
NEW_LINE Write one or more OS-specific line terminators to a file.
FFLUSH Physically write all pending output to a file.

Table 8 - 13. UTL_FILE Procedures

Security

The PL/SQL file I/O feature is available for both client side and server side PL/SQL. The client implementation is subject to normal operating system file permission checking, and so does not need any additional security constraints. But the server implementation might be running in a privileged mode, and so will need additional security restrictions that limit the power of this feature.

Note: The UTL_FILE package is similar to the client-side TEXT_IO package currently provided by Oracle Procedure Builder. Restrictions for a server implementation require some API differences between UTL_FILE and TEXT_IO. In PL/SQL file I/O, errors are returned to the caller using PL/SQL exceptions.

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

You specify the accessible directories for the UTL_FILE functions in the initialization file using the UTL_FILE_DIR parameter, as follows:

UTL_FILE_DIR = <directory name>

For example, if the initialization file for the instance contains the line

UTL_FILE_DIR = /usr/jsmith/my_app

then the directory /usr/jsmith/my_app is accessible to the FOPEN function. Note that a directory named /usr/jsmith/My_App would not be accessible on case-sensitive operating systems.

The parameter specification

UTL_FILE_DIR = *

has a special meaning. This entry in effect turns off directory access checking, and makes any directory accessible to the UTL_FILE functions.

Warning: The '*' option should be used with great caution. For obvious security reasons, Oracle does not recommend that you use this option in production systems. Also, do not include '.' (the current directory for UNIX) in the accessible directories list.

Warning: To ensure security on file systems that allow symbolic links, users must not be allowed WRITE permission to directories accessible by PL/SQL file I./O functions. The symbolic links and PL/SQL file I/O could be used to circumvent normal operating system permission checking, and allow users read/write access to directories to which they would not otherwise have access.

File Ownership and Protections

On UNIX systems, a file created by the FOPEN function has as its owner the owner of the shadow process running the instance. In the normal case, this owner is oracle. Files created using FOPEN are always writeable and readable using the UTL_FILE routines, but non-privileged users who need to read these files outside of PL/SQL might have to get their system administrator to give them access.

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

but the following file locations and filename are invalid:

FILE LOCATION             FILENAME
/appl/gl/log/backup       L10324.log          # subdirectory
/APPL/gl/log              L10324.log          # uppercase
/appl/gl/log              backup/L10324.log   #dir in name
/usr/tmp                  T10324.tmp       # not in INIT.ORA

There are no user-level file permissions. All file locations specified by the UTL_FILE_DIR parameters are valid, for both reading and writing, for all users of the file I/O procedures. This can override operating system file permissions.

Declared Types

The specification for the UTL_FILE package declares one PL/SQL type: FILE_TYPE. The declaration is

TYPE file_type IS RECORD (id BINARY_INTEGER);

The contents of FILE_TYPE are private to the UTL_FILE package. Users of the package should not reference or change components of this record.

Exceptions

The specification for the UTL_FILE package declares seven exceptions. These exceptions are raised to indicate error conditions. The exceptions are shown in Table 8 - 14.

Exception Name Description
INVALID_PATH File location or filename was invalid.
INVALID_MODE The open_mode parameter in FOPEN was invalid.
INVALID_FILEHANDLE The file handle was invalid.
INVALID_OPERATION The file could not be opened or operated on as requested.
READ_ERROR An operating system error occurred during the read operation.
WRITE_ERROR An operating system error occurred during the write operation.
INTERNAL_ERROR An unspecified error in PL/SQL.

Table 8 - 14. UTL_FILE Package Exceptions

In addition to these package exceptions, procedures in the UTL_FILE package can also raise predefined PL/SQL exceptions such as NO_DATA_FOUND or VALUE_ERROR.

Functions and Procedures

The remainder of this section describes the individual functions and procedures that make up the UTL_FILE package.

FOPEN

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

The parameters for this procedure are described in Table 8 - 15, and the syntax is shown below.

Syntax

FUNCTION FOPEN(location  IN VARCHAR2,
               filename  IN VARCHAR2,
               open_mode IN VARCHAR2)
    RETURN UTL_FILE.FILE_TYPE;

Parameters Description
location The operating system-specific string that specifies the directory or area in which to open the file.
filename The 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 A string that specifies how the file is to be opened (either upper- or lowercase letters can be used). The supported values, and the UTL_FILE package 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)

Table 8 - 15. FOPEN Function Parameters

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

Return Value

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.

Notes

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.

Attention: Operating system-specific parameters, such as C-shell environment variables under UNIX, cannot be used in the file location or file name parameters.

Exceptions

FOPEN can raise any of the following exceptions:

UTL_FILE.INVALID_PATH

UTL_FILE.INVALID_MODE

UTL_FILE.INVALID_OPERATION

IS_OPEN

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

The parameter for this function is described in Table 8 - 16, and the syntax is shown below.

Syntax

FUNCTION IS_OPEN(file_handle  IN FILE_TYPE)
    RETURN BOOLEAN;

Parameter Description
file_handle An active file handle returned by an FOPEN call.

Table 8 - 16. IS_OPEN Function Parameters

Return Value

TRUE or FALSE.

Exceptions

IS_OPEN does not raise any exceptions.

FCLOSE

FCLOSE closes an open file identified by a file handle. You could receive a WRITE_ERROR exception when closing a file, as there might be buffered data yet to be written when FCLOSE executes.

The parameters for this procedure are described in Table 8 - 17, and the syntax is shown below.

Syntax

PROCEDURE FCLOSE (file_handle IN OUT FILE_TYPE);

Parameter Description
file_handle An active file handle returned by an FOPEN call.

Table 8 - 17. FCLOSE Procedure Parameters

Exceptions

FCLOSE can raise the following exceptions:

UTL_FILE.WRITE_ERROR

UTL_FILE.INVALID_FILEHANDLE

FCLOSE_ALL

FCLOSE_ALL closes all open file handles for the session. This can be used as an emergency cleanup procedure, for example when a PL/SQL program exits on an exception.

Attention: 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

PROCEDURE FCLOSE_ALL;

Exception

FCLOSE_ALL can raise the exception:

UTL_FILE.WRITE_ERROR

GET_LINE

GET_LINE 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, a VALUE_ERROR exception is raised. If no text was read due to "end of file," 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 1022 bytes.

The parameters for this procedure are described in Table 8 - 18, and the syntax is shown below.

Syntax

PROCEDURE GET_LINE(file_handle     IN  FILE_TYPE,
                   buffer          OUT VARCHAR2);

Parameters Description
file_handle An 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 The data buffer to receive the line read from the file.

Table 8 - 18. GET_LINE Procedure Parameters

Exceptions

GET_LINE can raise any of the following exceptions:

UTL_FILE.INVALID_FILEHANDLE

UTL_FILE.INVALID_OPERATION

UTL_FILE.READ_ERROR

NO_DATA_FOUND

VALUE_ERROR

PUT

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 parameters for this procedure are described in Table 8 - 19, and the syntax is shown below.

Syntax

PROCEDURE PUT(file_handle  IN FILE_TYPE,
              buffer       IN VARCHAR2);

Parameters Description
file_handle An active file handle returned by an FOPEN call.
buffer The 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.

Table 8 - 19. PUT Procedure Parameters

Exceptions

PUT can raise any of the following exceptions:

UTL_FILE.INVALID_FILEHANDLE

UTL_FILE.INVALID_OPERATION

UTL_FILE.WRITE_ERROR

NEW_LINE

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

The parameters for this procedure are described in Table 8 - 20, and the syntax is shown below.

Syntax

PROCEDURE NEW_LINE (file_handle  IN FILE_TYPE,
                    lines        IN NATURAL := 1);

Parameters Description
file_handle An active file handle returned by an FOPEN call.
lines The number of line terminators to be written to the file.

Table 8 - 20. NEW_LINE Procedure Parameters

Exceptions

NEW_LINE can raise any of the following exceptions:

UTL_FILE.INVALID_FILEHANDLE

UTL_FILE.INVALID_OPERATION

UTL_FILE.WRITE_ERROR

PUT_LINE

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

The parameters for this procedure are described in Table 8 - 21, and the syntax is shown below.

Syntax

PROCEDURE PUT_LINE(file_handle  IN FILE_TYPE,
                   buffer       IN VARCHAR2);

Parameters Description
file_handle An active file handle returned by an FOPEN call.
buffer The text buffer that contains the lines to be written to the file.

Table 8 - 21. PUT_LINE Procedure Parameters

Exceptions

PUT_LINE can raise any of the following exceptions:

UTL_FILE.INVALID_FILEHANDLE

UTL_FILE.INVALID_OPERATION

UTL_FILE.WRITE_ERROR

PUTF

PUTF 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 (see the ``Syntax'' section below).
\n Substitute with the appropriate platform-specific line terminator.

The parameters for this procedure are described in Table 8 - 22, and the syntax is shown below.

Syntax

PROCEDURE PUTF(file_handle  IN FILE_TYPE,
               format       IN VARCHAR2,
               [arg1        IN VARCHAR2,
               ...arg5      IN VARCHAR2]);

Parameters Description
file_handle An active file handle returned by an FOPEN call.
format The format string that can contain text as well as the formatting characters '\n' and '%s'.
arg1..arg5 From one to five optional 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, an empty string is substituted for each '%s' for which there is no argument.

Table 8 - 22. PUTF Procedure Parameters

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, an empty string is substituted for each %s for which there is no matching argument.

Exceptions

PUTF can raise any of the following exceptions:

UTL_FILE.INVALID_FILEHANDLE

UTL_FILE.INVALID_OPERATION

UTL_FILE.WRITE_ERROR

FFLUSH

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.

The parameter for this procedure is described in Table 8 - 23, and the syntax is shown below.

Syntax

PROCEDURE FFLUSH (file_handle  IN FILE_TYPE);

Parameters Description
file_handle An active file handle returned by an FOPEN call.

Table 8 - 23. FFLUSH Procedure Parameters

Exceptions

FFLUSH can raise any of the following exceptions:

UTL_FILE.INVALID_FILEHANDLE

UTL_FILE.INVALID_OPERATION

UTL_FILE.WRITE_ERROR

Function/Procedure	Description	Refer to
`FOPEN`	Open a file for input or ouput. Create an output file if it does not exist.
`IS_OPEN`	Determine if a file handle refers to an open file.
`FCLOSE`	Close a file.
`FCLOSE_ALL`	Close all open file handles.
`GET_LINE`	Read a line of text from an open file.
`PUT`	Write a line to a file. Do not append a line terminator.
`PUT_LINE`	Write a line to a file. Append an OS-specific line terminator.
`PUTF`	A PUT procedure with formatting.
`NEW_LINE`	Write one or more OS-specific line terminators to a file.
`FFLUSH`	Physically write all pending output to a file.

Exception Name	Description
INVALID_PATH	File location or filename was invalid.
INVALID_MODE	The `open_mode` parameter in FOPEN was invalid.
INVALID_FILEHANDLE	The file handle was invalid.
INVALID_OPERATION	The file could not be opened or operated on as requested.
READ_ERROR	An operating system error occurred during the read operation.
WRITE_ERROR	An operating system error occurred during the write operation.
INTERNAL_ERROR	An unspecified error in PL/SQL.

Parameters	Description
`location`	The operating system-specific string that specifies the directory or area in which to open the file.
`filename`	The 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`	A string that specifies how the file is to be opened (either upper- or lowercase letters can be used). The supported values, and the UTL_FILE package 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)

Parameter	Description
`file_handle`	An active file handle returned by an FOPEN call.

%s	Substitute this sequence with the string value of the next argument in the argument list (see the ``Syntax'' section below).
\n	Substitute with the appropriate platform-specific line terminator.