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: [EMAIL PROTECTED] From: Bill Buchan <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
