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?

http://docs.oracle.com/cd/E11882_01/appdev.112/e10577/u_file.htm#i1003526 - what you have above doesn't seem to match the docs at all. — Mat, Nov 28 '12 at 15:07
@Mat: in what way? The code above should be valid and works on another Oracle 9 environment. — Robert Gallow, Nov 28 '12 at 15:21
remember that the oracle user (ie the user that the db is running under) needs read/write/execute on the directories in order to get there and write your file. does the oracle user have at least r+x on /appl ? — DazzaL, Nov 28 '12 at 15:22
@RobertGallow: even the docs for 9.2 state that the first param must be an uppercase directory object, and the second must not contain a path. — Mat, Nov 28 '12 at 15:32
@Mat not strictly true. directory objects are the way to go sure, but paths are still supported even in 11gR2. paths aren't supposed to be passed in parameter 2 but they are again tolerated (ignored). — DazzaL, Nov 28 '12 at 15:34

score 7 · Accepted Answer · answered Nov 28 '12 at 15:49

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

Looks like this was it. The parameter was set in the wrong way. Nice one Dazzal. Thanks. — Robert Gallow, Nov 30 '12 at 10:00

score 0 · Answer 2 · answered Nov 28 '12 at 14:55

0

2 resolution steps for overcoming this error::

1.Be sure that the OS level user has permission to write to a folder

2.Try upper case letters for the directory name i.e. instead of out_dir use OUT_DIR

answered Nov 28 '12 at 14:55

Ajith Sasidharan

1,155
7
7

Tried upper case and it still does not work. Do you know how I would check if the OS level user has permission. The target dir is 777. Does this not mean writable by all? – Robert Gallow Nov 28 '12 at 15:15
Does the file has write permission? – Ajith Sasidharan Nov 28 '12 at 15:24

score 0 · Answer 3 · answered Aug 12 '16 at 13:24

Out of space may also lead to this issue.

Oracle throws same error if you do not have space on mount point.

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

Please check whether space is available or not on utl_file_dir mountpoint.

For Linux & Solaris use :

df -k ${UTIL_FILE_PATH}

I faced same issue , later got to know it was space issue on mount point.

score -1 · Answer 4 · edited Nov 10 '15 at 14:39

-1

For the filename put just the name not the path. So you should put

declare

l_file_handle              UTL_FILE.FILE_TYPE;

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

  UTL_FILE.FCLOSE(l_file_handle);

  DBMS_OUTPUT.PUT_LINE('FINISHED');

end;

edited Nov 10 '15 at 14:39

theB

6,450
1
28
38

answered Nov 10 '15 at 07:39

user5545519

1

ORACLE 11G UTL_FILE + UTL_FILE_DIR - Invalid Directory

4 Answers4