xThe Oracle (tm) Users' Co-Operative FAQ

Why am I getting errors when I try to use the utl_file package to write a flat file ?


Author's name: Norman Dunbar

Author's Email: Oracle@bountifulsolutions.co.uk

Date written: 06/09/2002

Oracle version(s): 8.1.7.4 & 9.2.0

Why am I getting errors when I try to use the utl_file package to write a flat file ?

Back to index of questions


The following is an example procedure to dump a table in one of my schemas to a flat file on the server.

CREATE OR REPLACE PROCEDURE EmployeeFlatFile
IS
  file_id UTL_FILE.FILE_TYPE;
BEGIN
  file_id := utl_file.FOPEN( '/temp', 'test.txt', 'w' );
  FOR emp IN (SELECT employee_name FROM employee)
  LOOP
     utl_file.PUT_LINE( file_id, emp.employee_name );
  END LOOP;
  utl_file.fCLOSE(file_id);
END;
/

When I run it, I get the following not very helpful error message :

SQL> execute EmployeeFlatFile;
BEGIN EmployeeFlatFile; END;
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at "CMDB.EMPLOYEEFLATFILE", line 5
ORA-06512: at line 1

The first thing I need to do is insert some exception handling so that I can see exactly which of the user defined exceptions the UTL_FILE package is raising.

CREATE OR REPLACE PROCEDURE EmployeeFlatFile
IS
  file_id UTL_FILE.FILE_TYPE;
BEGIN
  file_id := utl_file.FOPEN( '/temp', 'test.txt', 'w' );
  FOR emp IN (SELECT employee_name FROM employee)
  LOOP
     utl_file.PUT_LINE( file_id, emp.employee_name );
  END LOOP;
  utl_file.fCLOSE(file_id);
EXCEPTION
  WHEN utl_file.invalid_path THEN
    RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_path');
  WHEN utl_file.invalid_mode THEN
    RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_mode');
  WHEN utl_file.invalid_filehandle THEN
    RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_filehandle');
  WHEN utl_file.invalid_operation THEN
    RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_operation');
  WHEN utl_file.read_error THEN
    RAISE_APPLICATION_ERROR(-20001, 'utl_file.read_error');
  WHEN utl_file.write_error THEN
    RAISE_APPLICATION_ERROR(-20001, 'utl_file.write_error');
  WHEN utl_file.internal_error THEN
    RAISE_APPLICATION_ERROR(-20001, 'utl_file.internal_error');
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001, 'utl_file.other_error');   
END;
/

Now when I run it, I get a more meaningful error message. If you are having UTL_FILE problems, then I suggest that you add the exception handling above to convert the not very helpful errors in the original version of my procedure to something like the following :

SQL> execute EmployeeFlatFile;
BEGIN EmployeeFlatFile; END;
*
ERROR at line 1:
ORA-20001: utl_file.invalid_path
ORA-06512: at "CMDB.EMPLOYEEFLATFILE", line 13
ORA-06512: at line 1

Now it all becomes clear - I have an invalid path specified. At least I know where to go to look for the cause(s) of the problem. UTL_FILE is very sensitive to its setup.

Setting up UTL_FILE

Up to Oracle 9.0.1 or 9i Release 1

set the UTL_FILE_DIR initialisation parameter in initSID.ora. You can have a single parameter with all allowed directories :

UTL_FILE_DIR = (/tmp, /oracle/oracle817/admin/CMDB/utl_file)

or a list :

UTL_FILE_DIR = /tmp
UTL_FILE_DIR = /oracle/oracle817/admin/CMDB/utl_file

Warning : You cannot have any other parameters between the two. If you do, then only the last directory will be used, the first will not be valid. The following is wrong :

UTL_FILE_DIR = /tmp
DB_BLOCK_SIZE = 8192
UTL_FILE_DIR = /oracle/oracle817/admin/CMDB/utl_file

In this case, only /oracle/oracle817/admin/CMDB/utl_file will be acceptable. If you try to use /tmp, you'll get an invalid path exception thrown.

Once you have changed the initSID.ora file, you must now close and restart the database.

Warning : do not set the UTL_FILE_DIR parameter to '*' as this will allow writing to and reading from any file in any location on your server - at least any directory that the Oracle user has read and /or write access to. This includes the locations where you have put you logfiles, datafiles etc, so anyone using UTL_FILE can corrupt your database. Best avoided !

Oracle 9.2 or 9i Release 2

In the latest release of 9i, the use of UTL_FILE_DIR is advised against on security grounds. The advice now is to create a directory and use that instead. By default, only SYS and SYSTEM can create directories. In addition, the user now writes procedures which use the directory name in FOPEN, rather than the path name. This means that if the DBA changes the location of the path the directory relates to, the user procedures are only slightly affected, but they will still work. See below for more details.

CREATE DIRECTORY utl_file_tmp AS '/tmp';
CREATE DIRECTORY utl_file_dir AS '/oracle/oracle817/admin/CMDB/utl_file';

GRANT READ ON DIRECTORY utl_file_tmp to username;
GRANT WRITE ON DIRECTORY utl_file_tmp to username;
GRANT READ ON DIRECTORY utl_file_dir to username;
GRANT WRITE ON DIRECTORY utl_file_dir to username;

All Oracle verions

Once the above has been sorted out according to your version of Oracle, you must ensure that the named directories exist on the server. UTL_FILE writes files to the server, not to the PC you are working on. So, over on the server create the appropriate directories, and try again :

cmdb> mkdir utl_file
cmdb> ls -l
drwxr-xr-x   2 cmdb       dba             96 Sep  6 13:11 utl_file

So the directory exists. Can we use it yet ?

I've edited my procedure to change the invalid path from '/temp' to the newly created '/oracle/oracle817/admin/CMDB/utl_file', what happens when I run it ? Yet again, an error message :

SQL> execute EmployeeFlatfile;
BEGIN EmployeeFlatfile; END;
*
ERROR at line 1:
ORA-20001: utl_file.invalid_operation
ORA-06512: at "CMDB.EMPLOYEEFLATFILE", line 19
ORA-06512: at line 1

This time, the error message is not invalid path, so obviously, I've got the path name correct, however, have another look above at the output from the 'ls -l' command, user cmdb owns the directory, and is in the dba group, but the only users with write access is the cmdb user. A quick change is required to give the dba group write privs too :

cmdb> chmod g+w utl_file
cmdb> ls -l
drwxrwxr-x   2 cmdb       dba             96 Sep  6 13:11 utl_file

And this time, when I run the procedure, it all 'just works'.

SQL> execute EmployeeFlatfile;
PL/SQL procedure successfully completed.

A quick check on the server :

cmdb> ls -l utl_file
-rw-r--r--   1 ora817     dba           5856 Sep  6 13:25 test.txt

So, in summary :

Possible problem areas

Windows has its own problems

On Unix everything is case sensitive so the rules above regarding making sure you specify the directory name exactly as per the UTL_FILE_DIR is important. On Windows, folder names are non-case sensitive so that rule could possibly not apply - or could it ?

SQL> connect system
Enter password: ******
Connected.

SQL> create directory utl_file_dir as 'c:\temp\utl_file';
Directory created.

SQL> grant write,read on directory utl_file_dir to scott;
Grant succeeded.

Then in a DOS session, create a folder which has the name slightly different from that already used :

C:\>mkdir c:\temp\UTL_FILE
C:\>

Back in SQLPlus again, we connect as Scott and create a procedute to dump the EMP table to a text file, similar to the examples above.

SQL> connect scott/tiger
Connected.

SQL> CREATE OR REPLACE PROCEDURE Empflatfile
  2  IS
  3    file_id UTL_FILE.FILE_TYPE;
  4  BEGIN
  5    file_id := utl_file.FOPEN( 'utl_file_dir', 'test.txt', 'w' );
  6    FOR emp IN (SELECT ename FROM emp)
  7    LOOP
  8       utl_file.PUT_LINE( file_id, emp.ename );
  9    END LOOP;
 10    utl_file.fCLOSE(file_id);
 11  EXCEPTION
 12    WHEN utl_file.invalid_path THEN
 13      RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_path');
 14    WHEN utl_file.invalid_mode THEN
 15      RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_mode');
 16    WHEN utl_file.invalid_filehandle THEN
 17      RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_filehandle');
 18    WHEN utl_file.invalid_operation THEN
 19      RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_operation');
 20    WHEN utl_file.read_error THEN
 21      RAISE_APPLICATION_ERROR(-20001, 'utl_file.read_error');
 22    WHEN utl_file.write_error THEN
 23      RAISE_APPLICATION_ERROR(-20001, 'utl_file.write_error');
 24    WHEN utl_file.internal_error THEN
 25      RAISE_APPLICATION_ERROR(-20001, 'utl_file.internal_error');
 26    WHEN OTHERS THEN
 27      RAISE_APPLICATION_ERROR(-20001, 'utl_file.other_error');   
 28  END;
 29  /
Procedure created.


SQL>  execute EmpFlatFile;
BEGIN EmpFlatFile; END;

*
ERROR at line 1:
ORA-20001: utl_file.invalid_path
ORA-06512: at "SCOTT.EMPFLATFILE", line 13
ORA-06512: at line 1

So it looks like Oracle doesn't like the fact that the folder name I used when creating a directory (I'm using Oracle 9irelease 2) is different from the one on the disc - or is it something else ?

Having tested the procedure above with both an uppercase folder name and a lower case one, neither worked. However, the manual mentions that the valid diurectory names that can be used are listed in ALL_DIRECTORIES, so a quick select shows the following :

SQL> column owner format a15
SQL> column directory_name format a20
SQL> column directory_path format a40
SQL> r
  1  select * from all_directories
  2*

OWNER           DIRECTORY_NAME       DIRECTORY_PATH
--------------- -------------------- ----------------------------------------
SYS             UTL_FILE_DIR         c:\temp\utl_file

So it looks like the directory name has been converted to the usual Oracle uppercase version. A quick change to the procedure to make the FOPEN call look like this :

file_id := utl_file.FOPEN( 'UTL_FILE_DIR', 'test.txt', 'w' );

Followed by an execute and we have success !

SQL> execute empflatfile
PL/SQL procedure successfully completed.

Check it out in a DOS session :

C:\Temp\UTL_FILE>dir
 Volume in drive C is LPC001813
 Volume Serial Number is F4B4-BCE0

 Directory of C:\Temp\UTL_FILE

06/09/2002  14:27       <DIR>          .
06/09/2002  14:27       <DIR>          ..
06/09/2002  14:27                   98 test.txt
               1 File(s)             98 bytes
               2 Dir(s)     910,671,872 bytes free

As you can see, the fact that the folder name on the disc is in upper case, while Oracle thinks it is in lower case makes no difference. Remember, this is not the case in Unix.

Windows shares and mapped drives

Oracle does not understand about mapped drives. When running as a service on a Windows server (or even using Personal Oracle on your PC), the directory or UTL_FILE_DIR cannot be a windows mapped drive. The services run as the LOCAL SYSTEM user, and has no access to any mapped drives you may have set up.

The following example shows how this doesn't work, and in addition, shows how a directory can be remapped, without causing too much hassle to users who have existing procedures which write or read from the directory - unlike if a specific UTL_FILE_DIR is used. (But, remember, the procedures will go invalid when the directory is dropped - causing a recompile on next execute!)

SQL> connect system
Enter password: ******
Connected.

SQL> drop directory utl_file_dir;
Directory dropped.

SQL> create directory utl_file_dir as 's:\temp\norman\utl_file';
Directory created.

SQL> grant read,write on directory utl_file_dir to scott;
Grant succeeded.

SQL> connect scott/tiger
Connected.

SQL> execute empflatfile
BEGIN empflatfile; END;
*
ERROR at line 1:
ORA-20001: utl_file.invalid_operation
ORA-06512: at "SCOTT.EMPFLATFILE", line 19
ORA-06512: at line 1

If I now change the directory to use the full UNC name of the share mapped as drive S:\ then the following shows what happens :

SQL> connect system
Enter password: ******
Connected.

SQL> drop directory utl_file_dir;
Directory dropped.

SQL> create directory utl_file_dir as '\\fp\shared\temp\norman\utl_file';
Directory created.

SQL> grant read,write on directory utl_file_dir to scott;
Grant succeeded.

SQL> connect scott/tiger
Connected.

SQL> execute empflatfile
BEGIN empflatfile; END;
*
ERROR at line 1:
ORA-20001: utl_file.invalid_operation
ORA-06512: at "SCOTT.EMPFLATFILE", line 19
ORA-06512: at line 1

So that didn't work - why not ?

In control panel, services (NT) or control panel, administrative tools, services - stop the OracleServiceXXXX service. Click on the login tab, and note that it is using a local system logon. That user has no rights to any shares (at least on our NT system,) and so is unable to access the shared drives by their mapped name or full UNC name.

If I change the service to logon as my own username and restart it, I can try again :

SQL> connect scott/tiger
Connected.

SQL> execute empflatfile
PL/SQL procedure successfully completed.

I could also go back and change the directory to use a mapped drive name (S:\temp\norman\utl_file) but if our sysadmins for NT decided to change the share name, or the mapped drive, the procedure would be broken again. It is advisable to use the full UNC name for the drive you are trying to access.

And finally, watch out for the times when you change your Windows password. When you do this, remember to amend your OracleServiceXXXX service again - otherwise the database won't startup.


Further reading:

Oracle 8.1.7 Supplied PL/SQL Packages and Type Reference - on the Oracle Docs CD.

Oracle 9i release 1 Supplied PL/SQL Packages and Type Reference - on the Oracle Docs CD.

Oracle 9i release 2 Supplied PL/SQL Packages and Type Reference - on the Oracle Docs CD.

Expert one-on-one Oracle by Tom Kyte. Appendix A


Back to top

Back to index of questions