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; /
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:
When you run UTL_FILE to write to file, you might encounter these errors – possible causes and fixes are given below.
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 /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.
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].
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.
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:
with possible causes and fixes for each error.
