UTL_FILE: Write To File Example and Debugging Common Errors

A simple example of using UTL_FILE in PLSQL to create a file and write into it. Plus, the common ORA errors you encounter with the use of UTL_FILE, and how to resolve them.

This script has been run on Oracle 10G Express Edition, on Windows OS.

Here’s the script:

-- Simple PLSQL to open a file,
-- write two lines into the file,
-- and close the file
declare 
  fhandle  utl_file.file_type;
begin
  fhandle := utl_file.fopen(
                'UTL_DIR'     -- File location
              , 'test_file.txt' -- File name
              , 'w' -- Open mode: w = write. 
                  );

  utl_file.put(fhandle, 'Hello world!'
                      || CHR(10));
  utl_file.put(fhandle, 'Hello again!');

  utl_file.fclose(fhandle);
exception
  when others then 
    dbms_output.put_line('ERROR: ' || SQLCODE 
                      || ' - ' || SQLERRM);
    raise;
end;
/

When run:

SQL> -- Simple PLSQL to open a file,
SQL> -- write two lines into the file,
SQL> -- and close the file
SQL> declare
  2    fhandle  utl_file.file_type;
  3  begin
  4    fhandle := utl_file.fopen(
  5                  'UTL_DIR'     -- File location
  6                , 'test_file.txt' -- File name
  7                , 'w' -- Open mode: w = write.
  8                    );
  9
 10    utl_file.put(fhandle, 'Hello world!'
 11                        || CHR(10));
 12    utl_file.put(fhandle, 'Hello again!');
 13
 14    utl_file.fclose(fhandle);
 15  exception
 16    when others then
 17      dbms_output.put_line('ERROR: ' || SQLCODE
 18                        || ' - ' || SQLERRM);
 19      raise;
 20  end;
 21  /

PL/SQL procedure successfully completed.

The output file:

UTL_FILE Errors and How To Resolve Them

When you run UTL_FILE to write to file, you might encounter these errors  – possible causes and fixes are given below.

1. PLS-00201: identifier ‘UTL_FILE’ must be declared

This error sometimes shows up when you run UTL_FILE for the first time on the database.

Diagnosis and fix:

(a) Check that UTL_FILE package exists and is valid.

SQL> select owner
  2       , object_type
  3       , status
  4  from all_objects
  5  where object_name = 'UTL_FILE';

OWNER    OBJECT_TYPE      STATUS
-------- ---------------- -------
SYS      PACKAGE          VALID
PUBLIC   SYNONYM          VALID

If you get a listing as above, all is well — move on to check 1(b).

If the package status is invalid, recompile the package.
If the package is not listed, run {ORACLE_HOME}/rdbms/admin/utlfile.sql to create it.

(b) Check if the logged in user has execute privilege on UTL_FILE

A missing grant is the most likely cause of the PLS-00201 error.

SQL> select grantee
  2  from all_tab_privs
  3  where table_name = 'UTL_FILE';

GRANTEE
------------------------------
PUBLIC

For UTL_FILE to work, the grantee should be either the user logged in, or PUBLIC. If this privilege is missing, log in as an admin user (e.g. SYS) and grant EXECUTE on UTL_FILE.

Log back in as the application user, and check the execute privilege on UTL_FILE.

The script should be able to recognize UTL_FILE now, without PLS-00201.

2. ORA-29280: invalid directory path

This indicates that the Oracle engine is unable to find the file location specified in utl_file.fopen.

Diagnosis and fix:

(a) Check if ‘file location’ in the script has a reference to a valid Oracle directory

UTL_FILE uses Oracle directories, not OS directories. Do not write file location like this:

D:\app

Instead, login as SYS and create an Oracle directory with a reference to a valid OS directory path.

SQL> create or replace directory utl_dir
  2  as 'D:\app';

Directory created.

Grant read and write privilege to the application user (or PUBLIC) on the new directory.

SQL> grant read, write
  2  on directory utl_dir
  3  to public;

Grant succeeded.

Note that the directory path can be case-sensitive on some operating systems like Unix.

(b) Check if ‘file location’ in the script exists on the Oracle server

The directory specified must exist on the Oracle server when the script is run. The ‘create directory’ command does not validate that the directory specified actually exists, nor does it create the directory on the server. It is the developer’s responsibility to specify a valid directory path.

A typical mistake is to specify a *local* machine folder as the UTL_FILE directory when the Oracle database server is on another machine — this will not work. The UTL_FILE directory must exist on the *server* machine.

More things to watch out for when using the create directory command.

Identify the file location:

SQL> select directory_name
  2       , directory_path
  3  from all_directories;

DIRECTORY_NAME   DIRECTORY_PATH
---------------- --------------
UTL_DIR          D:\dummypath

Fix the file location if required: login as SYS, and issue a create or replace directory:

SQL> create or replace directory utl_dir
  2  as 'D:\app';

Directory created.

Log back in as the application user and verify the change:

SQL> select directory_name
  2       , directory_path
  3  from all_directories;

DIRECTORY_NAME   DIRECTORY_PATH
---------------- --------------
UTL_DIR          D:\app

(c) Check if ‘file location’ in the script has write permissions for the logged in user

SQL> select grantee
  2       , privilege
  3  from all_tab_privs
  4  where table_name = 'UTL_DIR';

GRANTEE   PRIVILEGE
--------- ------------------------
PUBLIC    READ
PUBLIC    WRITE

If you get a listing as above, all is well — move on to check 2(c).

If you do not see WRITE permission granted to PUBLIC or to the logged in user, login as SYS and grant permissions.

SQL> grant read, write
  2  on directory utl_dir
  3  to public;

Grant succeeded.

(d) Check if ‘file location’ in the script is written in uppercase

A directory object is a database object, and database object names are in UPPERCASE by default. Even if the ‘create directory’ command you issued had the directory name in lowercase, unless you put it within quotes, the directory name will be stored in UPPERCASE.

So, the following script gives you ORA-29280 because the directory name ‘utl_dir’ [Line 5] is in lowercase.

SQL> -- Simple PLSQL to open a file,
SQL> -- write two lines into the file,
SQL> -- and close the file
SQL> declare
  2    fhandle  utl_file.file_type;
  3  begin
  4    fhandle := utl_file.fopen(
  5                  'utl_dir' -- File location
  6                , 'test_file.txt' -- File name
  7                , 'w' -- Open mode: w = write.
  8                    );
  9
 10    utl_file.put(fhandle, 'Hello world!'
 11                        || CHR(10));
 12    utl_file.put(fhandle, 'Hello again!');
 13
 14    utl_file.fclose(fhandle);
 15  exception
 16    when others then
 17      dbms_output.put_line('ERROR: ' || SQLCODE
 18                        || ' - ' || SQLERRM);
 19      raise;
 20  end;
 21  /
declare
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at line 19

Changing it to ‘UTL_DIR’ will resolve the error [see the first script in the article].

Notes

Before Oracle 9i, init.ora parameter called utl_file_dir was used in place of Oracle directories to specify the UTL_FILE file location.

utl_file_dir has been deprecated 9i onwards. Oracle recommends using Oracle directories which are more secure, easier to handle and do not require a database restart when created/modified. 

Summary

This post shows a simple working PL/SQL example of write to file with UTL_FILE. The example can be extended for more complex coding needs.

It also contains a guide to common errors encountered in UTL_FILE write to file:

• PLS-00201: identifier ‘UTL_FILE’ must be declared
• ORA-29280: invalid directory path

with possible causes and fixes for each error.