77
UTL_FILE

With the UTL_FILE package, your PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.

UTL_FILE I/O capabilities are similar to standard operating system stream file I/O (OPEN, GET, PUT, CLOSE) capabilities, but with some limitations. For example, you call the FOPEN function to return a file handle, which you use in subsequent calls to GET_LINE or PUT to perform stream I/O to a file. When file I/O is done, you call FCLOSE to complete any output and free resources associated with the file.

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 using PL/SQL exceptions.

This chapter discusses the following topics:

Security

The PL/SQL file I/O feature is available for both client-side and server-side PL/SQL. The client implementation (text I/O) is subject to normal operating system file permission checking. However, the server implementation may be running in a privileged mode, which requires a restriction on the directories that can be accessed. Accessible directories must be specified in the instance parameter initialization file (INIT.ORA).

Accessible directories for the UTL_FILE functions are specified in the initialization file using the UTL_FILE_DIR parameter. For example:

UTL_FILE_DIR = <directory name>

Note:

The directory specification is different on different platforms.

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 = * should be used with caution. It turns off directory access checking and makes all directories accessible to the UTL_FILE functions.

Caution:

Oracle does not recommend that you use the * option in production systems. Also, do not include '.' (the current directory for UNIX) in the accessible directories list.

To ensure security on file systems that enable 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, the owner of a file created by the FOPEN function is the owner of the shadow process running the instance. Normally, this owner is ORACLE. Files created using FOPEN are always writable and readable using the UTL_FILE subprograms, but nonprivileged users who need to read these files outside of PL/SQL may need access from a system administrator.

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

Caution:

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.

Types

TYPE file_type IS RECORD (id BINARY_INTEGER, datatype BINARY_INTEGER);

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

Exceptions

Table 77-1 UTL_FILE Package Exceptions

Exception Name	Description
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	Unspecified PL/SQL error.
CHARSETMISMATCH	A file is opened using `FOPEN_NCHAR`, but later I/O operations use nonchar functions such as `PUTF` or `GET_LINE`.

Procedures in UTL_FILE can also raise predefined PL/SQL exceptions such as NO_DATA_FOUND or VALUE_ERROR.

Summary of UTL_FILE Subprograms

Table 77-2 UTL_FILE Subprograms

Subprogram	Description
"FOPEN Function"	Opens a file for input or output with the default line size.
"FOPEN Function"	Opens a file with the maximum line size specified.
"FOPEN_NCHAR Function"	Opens a file in Unicode for input or output.
"FOPEN_NCHAR Function"	Opens a file in Unicode for input or output, with the maximum line size specified.
"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 text from an open file.
"GET_LINE_NCHAR Procedure"	Reads text in Unicode from an open file.
"PUT Procedure"	Writes a string to a file.
"PUT_NCHAR Procedure"	Writes a Unicode string to a file.
"NEW_LINE Procedure"	Writes one or more operating system-specific line terminators to a file.
"PUT_LINE Procedure"	Writes a line to a file. This appends an operating system-specific line terminator.
"PUT_LINE_NCHAR Procedure"	Writes a Unicode line to a file.
"PUTF Procedure"	A `PUT` procedure with formatting.
"PUTF_NCHAR Procedure"	A `PUT_NCHAR` procedure with formatting. Writes a Unicode string to a file, with formatting.
"FFLUSH Procedure"	Physically writes all pending output to a file.

77 UTL_FILE