ORACLE 11G UTL_FILE + UTL_FILE_DIR - Invalid Directory

Question

Hi I am running Oracle 11 and I am trying to write to a directory on the server box using UTL_FILE.FOPEN.

To test this I am using the following script (output included):

SQL> @D:\test.sql

declare

l_file_handle              UTL_FILE.FILE_TYPE;

begin
  l_file_handle := UTL_FILE.FOPEN('/appl/mydir',
                                  '/appl/mydir/filename',
                                  'W');

  UTL_FILE.FCLOSE(l_file_handle);

  DBMS_OUTPUT.PUT_LINE('FINISHED');

end;

ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 7

SQL>

I have the /appl/mydir added to the UTL_FILE parameter:

SELECT value
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';

/appl/mydir, /appl/mydir2, /appl/mydir3

The UNIX directory is writable by all on UNIX:

$ ls -l /appl/mydir
total 0
drwxrwsrwx    2 user userc 363968 Nov 27 13:46 mydir

Using oracle Directory object is not an option and I am aware of the disadvantages of using the UTL_FILE_DIR implementation.

Any ideas why the above script is failing?

Answer 1

first, in 11g the preferred way is to create a directory and not use utl_file.

secondly, please verify what exact command you used to set the directoty list :

SELECT value
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';

/appl/mydir, /appl/mydir2, /appl/mydir3

was it

alter system set utl_file_dir='/appl/mydir, /appl/mydir2, /appl/mydir3' scope = spfile;

or

alter system set utl_file_dir='/appl/mydir','/appl/mydir2','/appl/mydir3' scope = spfile;

if its the first way, redo it again the 2nd way as the first way is wrong (it will look the same in the v$table output, but its wrong).

eg:

declare
  2  
l_file_handle              UTL_FILE.FILE_TYPE;
  4  
begin
  l_file_handle := UTL_FILE.FOPEN('/tmp/foo/a',
                                  '/tmp/foo/a/filename.txt',
                                  'w');
  9  
  UTL_FILE.FCLOSE(l_file_handle);
 11  
  DBMS_OUTPUT.PUT_LINE('FINISHED');
 13  
end;
 15  /
declare
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 6


SQL> show parameter utl_fil

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                 string  /tmp/foo, /tmp/foo/a

humm. now lets fix that data.

SQL> alter system set utl_file_dir='/tmp/foo','/tmp/foo/a' scope = spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

Total System Global Area  263049216 bytes
Fixed Size          2225584 bytes
Variable Size         176163408 bytes
Database Buffers       79691776 bytes
Redo Buffers            4968448 bytes
Database mounted.
Database opened.
declare
  2  
l_file_handle              UTL_FILE.FILE_TYPE;
  4  
begin
  l_file_handle := UTL_FILE.FOPEN('/tmp/foo/a',
                                  '/tmp/foo/a/filename.txt',
                                  'w');
  9  
  UTL_FILE.FCLOSE(l_file_handle);
 11  
  DBMS_OUTPUT.PUT_LINE('FINISHED');
 13  
end;
 15  /

PL/SQL procedure successfully completed.

SQL> show parameter utl_file

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                 string  /tmp/foo, /tmp/foo/a
SQL> 

also verify the oracle user has r+x on /appl and rwx on /appl/mydir