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

I am trying to write in a file stored in c:\ drive named vin1.txt and getting this error .Please suggest!

> ERROR at line 1: ORA-29280: invalid
> directory path ORA-06512: at
> "SYS.UTL_FILE", line 18 ORA-06512: at
> "SYS.UTL_FILE", line 424 ORA-06512: at
> "SCOTT.SAL_STATUS", line 12 ORA-06512:
> 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

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.

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.

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.

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.

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');