Re: UTL_FILE ORA-29280: invalid directory path

Jake wrote:
>
> I'm using 10g. I keep getting a ORA-29280: invalid directory path
> when trying to open a file (I have to login as sysdba to even see
> UTL_FILE, but that's another story).
>
> I created the directory D:\oracle files
>
> I added this line to the end of init.ora:
>
> UTL_FILE_DIR = 'D:\oracle_files'
>
> Then, this happened:
>
> SQL> CREATE DIRECTORY testdir AS 'D:\oracle_files';
>
> Directory created.
>
> SQL> GRANT READ,WRITE ON DIRECTORY testdir TO jgarfield;
>
> Grant succeeded.
>
> SQL>
> SQL> DECLARE
> 2 v_file_handle UTL_FILE.FILE_TYPE;
> 3 BEGIN
> 4 v_file_handle :=
> 5 UTL_FILE.FOPEN('D:\oracle_files', 'testing.txt', 'A');
> 6 UTL_FILE.PUT_LINE(v_file_handle, 'Testing');
> 7 UTL_FILE.FCLOSE(v_file_handle);
> 8 END;
> 9 /
> DECLARE
> *
> ERROR at line 1:
> ORA-29280: invalid directory path
> ORA-06512: at "SYS.UTL_FILE", line 33
> ORA-06512: at "SYS.UTL_FILE", line 436
> ORA-06512: at line 4
>
> SQL> show parameter utl_file_dir
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> utl_file_dir string
> SQL> alter system set utl_file_dir='d:\oracle_files'
> 2 ;
> alter system set utl_file_dir='d:\oracle_files'
> *
> ERROR at line 1:
> ORA-02095: specified initialization parameter cannot be modified
>
>
> SQL> alter session set utl_file_dir='d:\oracle_files'
> 2 ;
> alter session set utl_file_dir='d:\oracle_files'
> *
> ERROR at line 1:
> ORA-02095: specified initialization parameter cannot be modified
>
> I guess the main problem is that I can't even set the UTL_FILE_DIR,
> even though I changed init.ora and restarted Oracle. What else do I
> need to do to be able to read and write a file? Thanks.

Assuming a currently supported version of the database the UTL_FILE_DIR parameter has been deprecated. Create a DIRECTORY object and use that.

http://www.psoug.org/reference/utl_file.html Scroll down to "Demo setup"

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org