UTL_FILE ORA-29280: invalid directory path
in [Oracle]
|
From: Jake on 4 Nov 2007 11:21 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.
From: Jake on 4 Nov 2007 11:36 okay so I thought maybe it was that i added single quotes to the line in init.ora, so I changed to: UTL_FILE_DIR = D:\oracle_files then I restarted. but still: SQL> show parameter utl_file_dir; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ utl_file_dir string what am I doing wrong here?
From: Ana C. Dent on 4 Nov 2007 13:09 Jake <jgarfield(a)earthlink.net> wrote in news:1194194161.900494.161960@ 19g2000hsx.googlegroups.com: > okay so I thought maybe it was that i added single quotes to the line > in init.ora, so I changed to: > > UTL_FILE_DIR = D:\oracle_files > > then I restarted. but still: > > SQL> show parameter utl_file_dir; > > NAME TYPE VALUE > ------------------------------------ ----------- > ------------------------------ > utl_file_dir string > > > > what am I doing wrong here? > If your datbase uses the spfile to obtain its parameters, then changing the pfile is an exercise in futility.
From: Jake on 4 Nov 2007 14:18 On Nov 4, 1:09 pm, "Ana C. Dent" <anaced...(a)hotmail.com> wrote: > Jake <jgarfi...(a)earthlink.net> wrote in news:1194194161.900494.161960@ > 19g2000hsx.googlegroups.com: > > > > > okay so I thought maybe it was that i added single quotes to the line > > in init.ora, so I changed to: > > > UTL_FILE_DIR = D:\oracle_files > > > then I restarted. but still: > > > SQL> show parameter utl_file_dir; > > > NAME TYPE VALUE > > ------------------------------------ ----------- > > ------------------------------ > > utl_file_dir string > > > what am I doing wrong here? > > If your datbase uses the spfile to obtain its parameters, > then changing the pfile is an exercise in futility. thanks. ALTER SYSTEM SET UTL_FILE_DIR='D:\oracle_files' scope=spfile and restarting makes everything work.
From: DA Morgan on 4 Nov 2007 14:46
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(a)x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org |