UTL_FILE: Simple Write To File Example and Debugging Common Errors

March 31, 2015

in Exceptions, ORA Errors, PL/SQL, Scripts, UTL packages

UTL_FILE

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 output

UTL_FILE Errors and How To Resolve Them

When you run UTL_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.

{ 13 comments… read them below or add one }

ganesh April 2, 2015 at 12:37 pm

it’s very good

Deepa April 22, 2015 at 10:24 am

finds very helpful

Deepa April 22, 2015 at 10:26 am

Very helpful

emega June 16, 2016 at 12:26 am

Thank you best written doc I found on utl_file_dir

arun July 29, 2016 at 6:27 am

Thanks. Really helpful !!!

Emmanuel August 16, 2016 at 10:22 am

Carefully and well thought out.

ramkishan October 29, 2016 at 9:19 am

Very helpful, thank you

Alex December 6, 2016 at 1:03 am

Fantastic write up! Best I’ve seen on the web so far. Thank you for this information.

Greg March 13, 2017 at 11:37 pm

Very good, I learned a lot from it and went through all examples. Everything is set properly, but it did not solve my problem:
Error report:
ORA-29283: invalid file operation
ORA-06512: at “SYS.UTL_FILE”, line 536
ORA-29283: invalid file operation
ORA-06512: at line 30
29283. 00000 – “invalid file operation”
*Cause: An attempt was made to read from a file or directory that does not exist, or file or directory access was denied by the operating system.
*Action: Verify file and directory access privileges on the file system, and if reading, verify that the file exists.

Greg March 13, 2017 at 11:41 pm

I have Windows 10. The directory’s attribute is read only and there is no way to change it. I can alter but when I save it and look at it again it is still read only.

Abhijit June 30, 2017 at 4:36 pm

Thanks , Perfectly Explained

Shri November 17, 2017 at 3:20 am

I am getting the same error as Greg. Did you figure it out Greg?

Pete November 21, 2017 at 9:45 pm

Whoa I was making the “typical mistake” of 2 (b) . THANK YOU for getting that sorted for me. UTL_FILE works now!

Leave a Comment

Previous post:

Next post: