|Oracle8i Supplied PL/SQL Packages Reference
Release 2 (8.1.6)
Part Number A76936-01
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 (
CLOSE), with some limitations. For example, call the
FOPEN function to return a file handle, which you then use in subsequent calls to
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.
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 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 (
Specify the accessible directories for the
UTL_FILE functions in the initialization file using the
UTL_FILE_DIR parameter. 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 turns off directory access checking, and it makes any directory accessible to the
The '*' option should be used with great caution. 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.
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.
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.
If the parameter initialization file contains only:
Then, the following file locations and filenames are valid:
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
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.
File location or filename was invalid.
File handle was invalid.
File could not be opened or operated on as requested.
Operating system error occurred during the read operation.
Operating system error occurred during the write operation.
Unspecified PL/SQL error.
In addition to these package exceptions, procedures in
UTL_FILE can also raise predefined PL/SQL exceptions such as