Re: utl_file.fopen and Oracle v9

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

When using DIRECTORY object, you have to use directory_name i.e. MY_OUT to point to the directory path. Change above line to,

f_loc varchar2(30) := 'MY_OUT';

Oracle will automatically resolve the directory path. However, if you use directory path as in your example, then Oracle looks for that directory to be specified as utl_file_dir.

When using directory object, make sure that the actual directory exists and has proper OS permissions. Also, Oracle stores directory_name as uppercase, so you must use uppercase directory name when referencing it. (select * from dba_directories).  

>
> setting utl_file_dir is not an Oracle recommandation, it is *MANDATORY*
> if you want to use the utl_file package.

Correction: It is *optional* to specify utl_file_dir parameter when using utl_file package in conjunction with 9i DIRECTORY object. DIRECTORY object can point to any *accessible* directory which may or may not be specified in utl_file_dir.

Regards
/Rauf Sarwar Received on Thu Jun 19 2003 - 17:28:44 CEST