Re: UTL_FILE access

In article <5sfd0v$q86_at_nntp.interaccess.com>, Ari Kaplan <akaplan_at_interaccess.com> writes
>Patrick Drouin (patrick.drouin_at_NO_SPAM.nortel.com) wrote:
>: Hello everyone,
>: I'm trying to use the UTL_FILE modules but I can't get them to work.
>: How do I find out which directory are accessible to my packages and how
>: do I had some more if I need to? I'm trying to store some basic output
>: of SQL queries in an HTML file that could be refresh every day.
>: Any help is welcome,
>: Patrick
>
>According to Steve Feuerstein's "PL/SQL Programming" book:
>"The location (of the file) you provide must have been listed as an
>accessible directory in the init.ora file for the database instance.
>When you pass the location as an argument to FOPEN, however, its format
>must differ slightly from the init.ora value."
>
>What that init.ora parameter is, the book does not mention. I tried
>the bdump, cdump, and udump directories, to no avail.
>
>Any suggestions out there?
>
>-Ari Kaplan
>Independent Oracle DBA Consultant
>
><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
><-> For 100+ Oracle tips, visit my Web Page: <->
><-> <->
><-> http://homepage.interaccess.com/~akaplan <->
><-> <->
><-> email: akaplan_at_interaccess.com <->
><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
>

Hi. The parameter init.ora parameter is utl_file_dir. This then specifies a list of directories that server side processes are allowed to write in. When you write your pl/sql code the directory pathname that you give to UTL_FILE.fopen must exactly match one of the directories that have been set up in init.ora. Depending upon which operating system you are using you may find that you have to match the pathnames exactly ie the case of every letter (I have certainly been recently caught out on this using NT 3.5 although I got it to work eventually). You can also check whether utl_file_dir is setup on a given system by querying the v$parameter view for the value of utl_file_dir but obviously this depends upon whether you have got select access to the v$ views

Hope this is helpful.

-- 
Andrew Thomas