Home » RDBMS Server » Server Administration » A very confusing ORA-22930 for UTL_FILE_DIR
A very confusing ORA-22930 for UTL_FILE_DIR [message #254821] Sat, 28 July 2007 14:16 Go to next message
afabbro
Messages: 1
Registered: July 2007
Junior Member
I'm quite confused. Probably because I'm using Windows and it senses I'm an Unix bigot. 10.2.0.2 is the version.

I created a directory on the RDBMS server's filesystem and set it as the UTL_FILE_DIR, then bounced the database. After restart, it shows as the current UTL_FILE_DIR:

SQL> show parameter utl_file_dir

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string D:\ORACLE\PRODUCT\10.2.0\ADMIN \MEMBETA\UTL_FILE_DIR

That directory, "D:\ORACLE\PRODUCT\10.2.0\ADMIN \MEMBETA\UTL_FILE_DIR", does exist. Really. And it was created while logged in as "oracle" on the Windows box.

But now...

SQL> grant read,write on directory utl_file_dir to someone;
grant read,write on directory utl_file_dir to someone
*
ERROR at line 1:
ORA-22930: directory does not exist

Eh?

It's like it's not finding it in DBA_DIRECTORIES, though of course it's not there since it's a "special" directory.

I've done this exact same sequence of steps on a Linux host and it works fine.
Re: A very confusing ORA-22930 for UTL_FILE_DIR [message #254823 is a reply to message #254821] Sat, 28 July 2007 15:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no relation between DIRECTORY object and utl_file_dir parameter (but they can both be used in utl_file package).
Use DIRECTORY, no more use utl_file_dir parameter.

Regards
Michel
Re: A very confusing ORA-22930 for UTL_FILE_DIR [message #254830 is a reply to message #254821] Sun, 29 July 2007 00:08 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
You have two options for specify data directories.

1. UTL_FILE_DIR parameter
1.Create physical directory

2. SQL>alter system set UTL_FILE_DIR=path SCOPE=spfile;

3. SQL>shutdown immediate;

4. SQL>startup 


2. Create Directory statement
1. Create physical directory

2. SQL>create directory DIRECTORY_NAME as 'path';

3. SQL>grant READ, WRITE on directory DIR_NAME to USER_NAME;



Regards
Taj
Previous Topic: Oracle 10g SQL Tuning Advisor
Next Topic: Create Database Working
Goto Forum:
  


Current Time: Sun Dec 11 00:07:01 CST 2016

Total time taken to generate the page: 0.04430 seconds