Oracle9i Supplied PL/SQL Packages and Types Reference
Release 1 (9.0.1)

Part Number A89852-02
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page


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.


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:


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>


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.


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:


Then the following file locations and filenames are valid:

/appl/gl/log              L10324.log
/appl/gl/out              O10324.out

But the following file locations and filename are invalid:

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



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


Table 77-1 UTL_FILE Package Exceptions
Exception Name  Description 

File location or filename was invalid. 


The open_mode parameter in FOPEN 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. 


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. 

Go to previous page Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index