UTL FILE FOPEN procedure not accepting path for directory

Question

I am trying to write in a file stored in c   drive named vin1 txt and getting this error  Please suggest    gt  ERROR at line 1  ORA-29280  invalid  gt  directory path ORA-06512  at  gt   SYS UTL FILE   line 18 ORA-06512  at  gt   SYS UTL FILE   line 424 ORA-06512  at  gt   SCOTT SAL STATUS   line 12 ORA-06512   gt  at line 1   HERE is the code    create or replace procedure sal status          p file dir IN varchar2      p filename IN varchar2       IS       v filehandle utl file file type      cursor emp Is         select   from employees         order by department id      v dep no departments department id TYPE       begin          v filehandle   utl file fopen p file dir p filename  w   --Opening a file          utl file putf v filehandle  SALARY REPORT  GENERATED ON  s n  SYSDATE            utl file new line v filehandle            for v emp rec IN emp LOOP             v dep no   v emp rec department id              utl file putf v filehandle  employee  s earns s n  v emp rec last name v emp rec salary                                end loop          utl file put line v filehandle     END OF REPORT               UTL FILE fclose v filehandle        end sal status   execute sal status  C     vin1 txt   --Executing

User · Accepted Answer

Since Oracle 9i there are two ways or declaring a directory for use with UTL_FILE.

The older way is to set the INIT.ORA parameter UTL_FILE_DIR. We have to restart the database for a change to take affect. The value can like any other PATH variable; it accepts wildcards. Using this approach means passing the directory path...

UTL_FILE.FOPEN('c:\temp', 'vineet.txt', 'W');

The alternative approach is to declare a directory object.

create or replace directory temp_dir as 'C:\temp'
/

grant read, write on directory temp_dir to vineet
/

Directory objects require the exact file path, and don't accept wildcards. In this approach we pass the directory object name...

UTL_FILE.FOPEN('TEMP_DIR', 'vineet.txt', 'W');

The UTL_FILE_DIR is deprecated because it is inherently insecure - all users have access to all the OS directories specified in the path, whereas read and write privileges can de granted discretely to individual users. Also, with Directory objects we can be add, remove or change directories without bouncing the database.

In either case, the oracle OS user must have read and/or write privileges on the OS directory. In case it isn't obvious, this means the directory must be visible from the database server. So we cannot use either approach to expose a directory on our local PC to a process running on a remote database server. Files must be uploaded to the database server, or a shared network drive.

If the oracle OS user does not have the appropriate privileges on the OS directory, or if the path specified in the database does not match to an actual path, the program will hurl this exception:

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 7

The OERR text for this error is pretty clear:

29283 -  "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.

User · Answer

For utl file open location filename mode    we need to give directory name for location but not path  For Example DATA FILE DIR   this is the directory name and check out the directory path for that particular directory name

User · Answer

You need to register the directory with Oracle  fopen takes the name of a directory object  not the path  For example    you may need to login as SYS to execute these   CREATE DIRECTORY MY DIR AS  C      GRANT READ ON DIRECTORY MY DIR TO SCOTT    Then  you can refer to it in the call to fopen   execute sal status  MY DIR   vin1 txt

User · Answer

Don t forget also that the path for the file is on the actual oracle server machine and not any local development machine that might be calling your stored procedure   This is probably very obvious but something that should be remembered

User · Answer

You need to have your DBA modify the init ora file  adding the directory you want to access to the  utl file dir  parameter   Your database instance will then need to be stopped and restarted because init ora is only read when the database is brought up   You can view  but not change  this parameter by running the following query   SELECT     FROM V PARAMETER   WHERE NAME    utl file dir    Share and enjoy

User · Answer

The directory name seems to be case sensitive  I faced the same issue but when I provided the directory name in upper case it worked

[oracle] UTL_FILE.FOPEN() procedure not accepting path for directory?

Examples related to oracle

Examples related to plsql

Examples related to oracle9i

Examples related to utl-file