Fwd: UTL_FILE_DIR on 9iR2

Ah! - got it.

To simplify my question I hadn't mentioned that I had more than one allowed directory, and this was where the problem lay:

I did:

ALTER SYSTEM SET UTL_FILE_DIR = 'directory1, directory2' SCOPE=spfile;

Whereas what is required is:

ALTER SYSTEM SET UTL_FILE_DIR = 'directory1','directory2' SCOPE=spfile;

The first form is accepted by Oracle and looks ok in v$parameter but clearly doesn't do the same thing underneath! Good: this means I can delay implementing DIRECTORYs for a bit longer... (not that I don't want to use them, just that other things have priority)

• Bill

>Date: Fri, 16 Jan 2004 11:15:35 +0000
>To: ORACLE-L_at_fatcity.com
>From: Bill Buchan <wbuchan_at_uk.intasys.com>
>Subject: UTL_FILE_DIR on 9iR2
>
>
>
>Hi all,
>
>I'm looking for some clarification on UTL_FILE_DIR on 9.2.
>
>I understand that this parameter is "obsolete" (Metalink Note 196939.1)
>and that CREATE DIRECTORY should be used instead. However we wanted to
>persist with the "old method" since we already have lots of existing
>PL/SQL that uses a look up table to decide where to read/write files; yes
>we'd move to CREATE DIRECTORY eventually but not right now. I thought
>that although UTL_FILE_DIR was "obsolete" it was really just deprecated
>and we could continue using it as before.
>
>If UTL_FILE_DIR = '*' then all is indeed well. However if I try to
>restrict the directories, i.e. UTL_FILE_DIR = '/only/allowed/here' then it
>does not work (yes, checked filesystem privs).
>
>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 line 4
>
>
>I can sort of fudge it to work:
>
>SQL> create directory "/only/allowed/here" as '/only/allowed/here';
>
>Directory created.
>
>SQL> grant read on directory "/only/allowed/here" to public;
>
>Grant succeeded.
>
>
>It's annoying to have to create directories with quoted names the same as
>the paths. Have I missed something or am I just going to have to use
>CREATE DIRECTORY like this until we have an opportunity to change the PL/SQL?
>
>Thanks
>- Bill.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bill Buchan
  INET: wbuchan_at_uk.intasys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).