Applies to:

PL/SQL - Version 7.3.4.5 to 11.2.0.1.0
Information in this document applies to any platform.
Checked for relevance on 3-MAR-2012.

Purpose

A Quick Overview of the UTL_FILE package

Scope

The UTL_FILE package is available to read and write to operating system flat files.

The UTL_FILE package is available on all platforms, but this article focuses on the UNIX platform only.

Details

Introduction

The UTL_FILE package is available to read and write to operating system flat files. The UTL_FILE package is available on all platforms, but this article focuses on the UNIX platform only.

Several procedures allow you to open, put text into, get text from, and close the file. The UTL_FILE runs on the server side, not from the client. This means that it will only manipulate files that are on a directory on the server and not on the local machine if you are connecting to a remote server. TEXT_IO is the package which allows you to read and write from a flat file from a PL/SQL client (e.g. Oracle Forms).

The package procedures and functions are described in details in the documentation

Oracle Database PL/SQL Packages and Types Reference

Permissions

Pre 9iR2 (9.2)

UTL_FILE used it's own mechanism to specify which files were accessible via the UTL_FILE_DIR parameter in the init.ora file. UTL_FILE_DIR lets you specify one or more directories (comma separated) that Oracle should use for PL/SQL file I/O.

If you list multiple values, all entries of this parameter must be on contiguous lines of the parameter file. If you separate them with other parameters, Oracle will read only the last (contiguous) lines. The number of directories that can be listed is limited by the 255 character buffer size.

All users can read or write to all files specified by this parameter. Therefore all PL/SQL users must be trusted with the information in the directories specified by this parameter.
After changing the value of the parameter UTL_FILE_DIR, you have to bounce the database in order to pick the new settings.

ALTER SYSTEM SET utl_file_dir= '', '', '' scope= spfile

Starting from 9iR2

As of 9.2, this functionality was expanded so that you were able to specify an Oracle DIRECTORY object instead of the exact directory name, thus avoiding a lot of hard coding.

Each directory to be accessed by UTL_FILE can be specified via the CREATE DIRECTORY command. And specified via this level of indirection in UTL_FILE.FOPEN.

It is the responsibility of the system and database administrators to implement appropriate file and directory security on the database host. UTL_FILE won't attempt to check for permission before executing an open/read/write/delete request. UTL_FILE will blindly issue any action requested and look for success or failure return status from the operating system.

By default UTL_FILE will have no file access because it will have no directory access, until granted access by CREATE DIRECTORY by SYS or SYSTEM or a user with DBA privileges. Since access privileges are granted on a per directory basis, the DBA can control directory access by either (1) creating separate directories for users with differing access requirements, or (2) use operating system utilities and features for controling read and write access by users.

Care must be taken to use uppercase for the DIRECTORY object in the call to utl_file.fopen, else you may encounter the ORA-29280: invalid directory path error.

Note:

In both cases (pre and post 9iR2), the directories that UTL_FILE can read from and write to must be accessible by the Oracle instance owner since the shadow processes run as that user

By default, Oracle creates the file, then the permissions on it are rw-r--r--

To give read/write permissions to all users on the directory, use chmod command.

i.e chmod 777

Example Usage

The following is an example demonstrating how to set up your environment, and includes a small PL/SQL procedure that writes out to an operating system flat file.

1. Set up your user so that they can get access to the file.

The recommended way of doing this is to create a DIRECTORY object since it is more flexible to use and also allows finer grained control over who has access. It also means that you do not have to edit the init.ora.

Note that in this example the user of the procedure creates the directory object. Note that to do this they must have been granted CREATE ANY DIRECTORY. In a normal production environment, the directory would be created by the DBA and access to the directory granted to the user.

create or replace directory UTF_DIR as '/home/usupport/forums';

If you do not wish to use a DIRECTORY then the UTL_FILE_DIR parameter must be added to the init.ora file.

This parameter should only be listed once. If there are numerous directory paths, then all paths should be listed with commas or spaces separating them.

Note that the wildcard (*) can be used. However, setting 'UTL_FILE_DIR = *' makes any directory accessible to the UTL_FILE functions and should only be used with great caution. Oracle does
not recommend using the '*' on a production system.

Examples:
UTL_FILE_DIR = '/u05/home/output/mydir', '/output'
or
UTL_FILE_DIR = *

** VERY IMPORTANT ** the Oracle instance must be bounced for the changes in the init.ora file to take place.

2. Write the PL/SQL procedure.

Use the following script to set up the table used in this example.

drop table TESTTAB;

create table  TESTTAB (
   C1        NUMBER,
   C2        NUMBER
)
/

insert into TESTTAB values(10,25);
insert into TESTTAB values(20,50);

commit;

Create the Procedure:

create or replace procedure UTL_FILE_EXAMPLE is

   FILE_HANDLE utl_file.file_type; -- file handle of OS flat file
   COL1  number;                   -- C1 retrieved from testtab table
   RETRIEVED_BUFFER varchar2(100); -- Line retrieved from flat file

-- The old form is included for reference:
-- DIR_NAME varchar2(40) := '/home/usupport/forums';
-- But this example uses the DIRECTORY created above.
   DIR_NAME varchar2(40) := 'UTF_DIR';
   FILE_NAME varchar2(40) := 'myfile.txt';

begin
   -- Open file to write into and obtain its file_handle.
   FILE_HANDLE := utl_file.fopen(DIR_NAME,FILE_NAME,'W');
   -- Write a line of text out to the file.
   utl_file.put_line(FILE_HANDLE, 'This is line 1 as a test');

   -- Select the c1 column from the testtab table where C2 is 25.
   select C1
    into COL1
    from TESTTAB
    where C2 = 25;

   -- Using PUTF write text with the col1 argument out to the file.
   utl_file.putf (
      FILE_HANDLE,
      'C1 is  %s when C2 is %s.\n',
      COL1,
      '25'
   );

   -- Close the file.
   utl_file.fclose(FILE_HANDLE);

   -- Open the same file to read from.
   file_handle := utl_file.fopen(DIR_NAME,FILE_NAME,'R');

   -- Read a line from the file.
   utl_file.get_line (FILE_HANDLE, RETRIEVED_BUFFER);

   -- Print fetched line out to the SQL*Plus prompt.
   dbms_output.put_line(RETRIEVED_BUFFER);

   -- Close the file.
   utl_file.fclose(FILE_HANDLE);
exception

   when no_data_found then
      dbms_output.put_line('No Data Found');
      utl_file.fclose(FILE_HANDLE);
   when utl_file.invalid_path then
      dbms_output.put_line('UTL_FILE.INVALID_PATH');
      utl_file.fclose(FILE_HANDLE);
   when utl_file.read_error then
      dbms_output.put_line(' UTL_FILE.READ_ERROR');
      utl_file.fclose(FILE_HANDLE);
   when utl_file.write_error then
      dbms_output.put_line('UTL_FILE.WRITE_ERROR');
      utl_file.fclose(FILE_HANDLE);
   when utl_file.invalid_operation then
      dbms_output.put_line('UTL_FILE.INVALID_OPERATION');
      utl_file.fclose(FILE_HANDLE);
   when others then
      dbms_output.put_line('Unhandled Error : '||sqlcode);
      dbms_output.put_line(sqlerrm);
      utl_file.fclose(FILE_HANDLE);
end;
/

3. Run the procedure:

SQL> set serverout on
SQL> exec utl_file_example
This is line 1 as a test

PL/SQL procedure successfully completed.

4. Check the Output:

The following is a listing of the /home/support/forums directory
before running the PL/SQL program:

-rw-r--r-- 1 usupport dba 2279 Dec 27 16:52 test.sql

after running the PL/SQL program:

-rw-r--r-- 1 osupport dba 62 Dec 27 16:53 myfile.txt
-rw-r--r-- 1 usupport dba 2279 Dec 27 16:53 test.sql

The following are the contents of the myfile.txt file:

This is line 1 as a test
C1 is 10 when C2 is 25.

Frequently asked Questions and Answers:

Q: I have added the directory path to my init.ora file and bounced my
database, but still receive the UTL_FILE.INVALID_PATH exception.

A: Verify that you do not have a '/' at the end of the directory path in
either the init.ora file or your code. Also, confirm that paths
match exactly, unless the wildcard (*) is being used.

Q: Why must I give the Oracle owner permissions on the directories
I am reading or writing to?

A: Because the file is opened by the oracle shadow process which normally runs as the Unix user that owns the Oracle executable.

Q: What is the maximum size of the buffer that I can read from or write
to a file in any PUT_LINE or GET_LINE procedure?

A: Prior to 8.0.5:

The maximum size for the buffer in any PUT_LINE procedure is 1023 bytes.
The maximum size for the buffer in any GET_LINE procedure is 1022 bytes.

From 8.0.5 onwards, UTL_FILE was enhanced to allow FOPEN to specify
a maximum size of up to 32767 characters. For additional information,
refer to Bug 458336, entitled "UTL_FILE.WRITE_ERROR RAISED DOING
MULTIPLE PUTS OF <1023 CHARS EACH (TOTAL>1023)"

The total number of bytes PUT in any file is dependent on the operating
system.

MORE ARTICLES ON UTL_FILE

Note.196939.1 Using CREATE DIRECTORY Instead of UTL_FILE_DIR init.ora Parameter
Note.202159.1 How to specify multiple utl_file_dir entries when using a server side parameter file (spfile)
Note.1016653.4 Steps to verify setup for UTL_FILE Package on Windows
Note.1063519.6 'ORA-29280 invalid directory path' is returned when using spaces within the directory
Note.108506.1 utl_file_dir Setting Ignored/Not Verified in init.ora
Note.432598.1 Utl_file.Fopen Fails when Sqlcase is set to Upper
Note.567594.1 SYS.Utl_file Is Generating ORA-29280 Utl_file.Invalid_path In Rac Environment Only
Note.1029333.6 UTL_FILE.FOPEN() RETURNS EXCEPTION INVALID_PATH
Note.200414.1 INVALID_PATH Exception using UTL_FILE_DIR Parameter
Note.152979.1 Unable to Write to a File Using UTL_FILE Package on AIX
Note.33755.1 PACKAGE UTL_FILE Specification
Note.358441.1 What is The Maximum Number of Files That Can Be Opened Using UTL_FILE.FOPEN()
Note 352685.1 How To Output Text From Pl/Sql Procedure Via A Job
Note 119644.1 How to use UTL_FILE Package to Read, Write and Append to O/S Files
Note.74268.1 Using utl_file, how file permissions are determined, working sample:
Note.74042.1 Example Pragma Restrict_References in UTL_FILE Package
Note.730774.1 FAQ and Known Issues While Using UTL_FILE
Note.453325.1 How To display the DBMS_OUTPUT From The Remote Procedure on Local Database ?
Note.443395.1 How To Save Table Data From an Oracle DB Into a User-Readable Text File ( .txt Extension) .:
Note.292961.1 ORA-29280 Invalid Directory Path With UTL_FILE.FOPEN:
Note.1063519.6 'ORA-29280 invalid directory path' is returned when using spaces within the directory:
Note.1026951.6 READ/WRITE Exceptions When Using UTL_FILE Due to Size Restrictions
Note.1040782.6 INVALID_PATH USING FOPEN FOR UTL_FILE
Note.329464.1 Invalid Path Error When Accessing a File On The Remote Machine With Utl_File Package
Note.1034188.6 INVALID_OPERATION Exception from UTL_FILE when Writing to/from Network Drive
Note.99133.1 utl_file fopen invalid operation on NFS client
Note.152753.1 Cannot Open More than 10 Files with UTL_FILE

Collaborate with and learn from your Peers, Industry Experts and Oracle Support Product Specialists using My Oracle Support Community. Join us here:

Oracle Community - https://communities.oracle.com/

Oracle PL/SQL Community - https://community.oracle.com/community/support/oracle_database/pl_sql