Re: UTL_FILE on multiple platforms

All

The location of the UTL_FILE dir must be stored in the v$session table of oracle 8.x databases therefore one option would be to, as others have sugested:

SELECT VALUE INTO l_file_name FROM v$parameter WHERE NAME = 'ult_file';

my_file := ULT_FILE.FOPEN( l_file_name,'utl_file_SetSynchScripts.txt', 'a' );

the ult_file location can then be made operating specific. Be careful though as the ult_file parameter can hold multiple file locations. If this is the case substr(VALUE,instr(VALUE,';')-1) can be used to get the first file dir. The ult_file parameter is configure via the strat-up file for the database.

In Oracle 9 the utl_file parameter is superseded by the create directory SQL command:

create or replace directory DATA_FILE_DIR as 'E:\oracle\ora92\demo\schema\sales_history\';

this can then be passed to utl file as

   my_file := ULT_FILE.FOPEN( 'DATA_FILE_DIR ','utl_file_SetSynchScripts.txt', 'a' );

To address Daniel's concerns create directory is grantable and can be set at any time

Regards

Graham

"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E5FD627.C37FC6AF_at_exesolutions.com...
> Thomas Gaines wrote:
>
> > mobiGeek wrote:
> >
> > > I need to have my PL/SQL package run on both Windows and Solaris. In
> > > my code, I am using utl_file to log debugging info.
> > >
> > > Currently my code looks like:
> > >
> > > v_dbg_file := utl_file.FOPEN( 'c:\tmp',
> > > 'utl_file_SetSynchScripts.txt', 'a' );
> > >
> > > Is there a generic way to have FOPEN create its file without
> > > specifying the directory?
> > >
> >
> > Don't know.
> >
> > >
> > > Failing that, is there a way that I can programmatically determine the
> > > OS (or the machine name) on which the code is running so I can do
> > > something like:
> > > IF [running_on_windows] THEN
> > > v_my_utl_directory := 'c:\tmp';
> > > ELSE
> > > v_my_utl_directory := '/tmp';
> > > END IF;
> > >
> >
> > I'm sure that there's a more elegant way to do this, but one approach
> > that immediately comes to mind is to check the value of a particular
> > initialization parameter. For example, check the value of
"control_files"
> >
> > (i.e. select value from v$parameter where name = 'control_files';,
> > assuming
> > that you have the proper privileges). If the values contain a Unix-type
> > file spec, then your OS is Unix. You get the idea.
> >
> > Bye,
> > TG
> >
> > >
> > > Thanks in advance,
> > > greg_fenton.
>
> What you suggest will work just as looking at the placement of datafiles
will
> work ... does the name contain forward or backward slashes.
>
> What I find a total showstopper is the thought that you can get a SysAdmin
or
> DBA to give permissions to /tmp just because that is how you configured
your
> product. I'd say "fat chance". The location of the utl_file directory
should
> be dynamically installation configurable: Not hard coded into a procedure.
>
> Anyone thinking they are going to sell a commercial product that hard
codes a
> path is going to get more grave disappointment than sales.
>
> Daniel Morgan
>
Received on Fri Feb 28 2003 - 16:00:57 CST