Re: utl_file.fopen and Oracle v9

From: Mike <y1799_at_yahoo.com>
Date: 19 Jun 2003 08:36:12 -0700
Message-ID: <93f42f92.0306190736.a9d3b09_at_posting.google.com>


sybrandb_at_yahoo.com wrote in message news:<a1d154f4.0306190159.1f9198c5_at_posting.google.com>...
> "Julia Sats" <julia_sats_at_sympatico.ca> wrote in message news:<SA7Ia.6888$5d.626481_at_news20.bellglobal.com>...
> > Hi,
> >
> > This question for Oracle version 9.
> > I do not use UTL_FILE_DIR = <directory name> as Oracle recommend
> >
> > I use such lines:
> > SQL> create directory my_out as 'c:\my_dir';
> > Directory created.
> >
> > SQL> grant read on directory my_out to public;
> > Grant succeeded.
> >
> > SQL> grant write on directory my_out to public;
> > Grant succeeded.
> >
> >
> > After it I created such procedure :
> > create or replace procedure out_test
> > as
> > my_file utl_file.file_type;
> > f_loc varchar2(10) := 'c:\my_dir';
> > f_out varchar2(10) := 'out.txt';
> > f_mode varchar2(2) := 'w';
> > f_len binary_integer := 80;
> >
> > f_str varchar2(20);
> > begin
> > my_file := utl_file.fopen(f_loc, f_out, f_mode, f_len);
> >
> > f_str := 'Hello';
> > utl_file.put_line(my_file, f_str);
> >
> > utl_file.fclose(my_file);
> > end;
> > /
> >
> >
> > and for line my_file := utl_file.fopen(f_loc, f_out, f_mode, f_len);
> > I got such error:
> > 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.OUT_TEST", line 11
> > ORA-06512: at line 1
> >
> >
> > Can anybody propose what to do
> >
> > Thanks
>
>
> setting utl_file_dir is not an Oracle recommandation, it is *MANDATORY*
> if you want to use the utl_file package.
>
> set utl_file_dir and bounce the database
>
> Regards
>
> Sybrand Bakker,
> Senior Oracle DBA

This is the copy from Oracle 9 Documentation:

In the past, accessible directories for the UTL_FILE functions were specified in the initialization file using the UTL_FILE_DIR parameter. However, UTL_FILE_DIR access is not recommended. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIR. Directory objects offer more flexibility and granular control to the UTL_FILE application administrator, can be maintained dynamically (that is, without shutting down the database), and are consistent with other Oracle tools. CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default. Received on Thu Jun 19 2003 - 17:36:12 CEST

Original text of this message