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

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, and it does not need any additional security constraints. However, the server implementation might be running in a privileged mode, and will need additional security restrictions that limit the power of this feature.

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 = <directory name>

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 turns off directory access checking, and it makes any directory accessible to the UTL_FILE functions.

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 writable and readable using the UTL_FILE subprograms, but non-privileged users who need to read these files outside of PL/SQL might nned 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

Types

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

Table 60-1 UTL_FILE Package Exceptions

INVALID_PATH

INVALID_MODE

INVALID_FILEHANDLE

INVALID_OPERATION

READ_ERROR

WRITE_ERROR

INTERNAL_ERROR

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