On Nov 4, 11:21 am, Jake <jgarfi..._at_earthlink.net> 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.
2 things.
If you are doing this in oracle forms. You must restart your
application server.
Second you may want to try to use the Alias you gave instead of the
file location.
Since you setup an alias that should be the location.
Old SQL
UTL_FILE.FOPEN('D:\oracle_files', 'testing.txt', 'A');
New SQL
UTL_FILE.FOPEN('testdir', 'testing.txt', 'A');
Received on Thu Nov 15 2007 - 09:32:48 CST
