SQL & PL/SQL

The UTL_FILE_DIR parameter has been deprecated by oracle in favour of direcory objects because of it's security problems.

The correct thing to do is to create a directory object e.g.:

CREATE OR REPLACE DIRECTORY mydir AS 'c:\myfiles';

Note: This does not create the directory on the file system. You have to do that yourself and ensure that oracle has permission to read/write to that file system directory.

Then, grant permission to the users who require access e.g....

GRANT READ,WRITE ON DIRECTORY mydir TO myuser;

Then use that directory object inside your FOPEN statement e.g.

fh := UTL_FILE.FOPEN('MYDIR', 'myfile.txt', 'r');

Note: You MUST specify the directory object name in quotes and in UPPER case for this to work as it is a string that is referring to a database object name which will have been stored in uppercase by default.

Thanks BlueShadow.

Can I create the directory object on the unix directory path?

e.g.

CREATE OR REPLACE DIRECTORY mydir AS '/tmp/myfiles/csv';

If so, how will my local client running on Toad understand that it has to create the file at this path on the unix server.

Another thing...
To who? Do the Oracle processes have permission to write into that directory or just your user that you are logged into unix with?

The user that I am logged into unix with.

But I supposed if the directory path is mentioned in init.ora, dba automatically gets write grant on it

Yes you can create directories as unix paths too.

The path where UTL_FILE writes to is on the Oracle server so it doesn't matter whether you run your code from TOAD or SQL*Plus, because the code is actually running on the database server, not in the client tool (which is just an editor really).

init.ora is an Oracle file. Oracle can't override the filesystem, so the permissions have to be set on the directory itself for the user that installed the oracle software (usually it will be "oracle" or some such user).

Don't worry about the init.ora file, in fact you would be better to clear out the utl_file_dir parameter and ensure that all your processes use the directory objects instead.

When you create a directory object this creates something on the database that points to a path specification on the file system. It's up to you to ensure that that path is valid and that permissions are granted on that path at the operating system level. The GRANT that you use on Oracle just lets the directory object be used by a database user.

:(

SQL> create or replace directory mydir as '/tmp/myfiles/csv';
create or replace directory mydir as '/tmp/myfiles/csv'
*
ERROR at line 1:
ORA-01031: insufficient privileges

Unfortunately I am not having system privileges on this box and so cant create through dba account.

Does this mean there is no way forward...

PS: I am using that schema to connect to SQL*Plus, on which my pl/sql procedure resides.

This is part of valid security to prevent people accessing the filesystem (hence why the utl_file_dir parameter in the init.ora file has been deprecated because it was a security flaw).

Either...
Your database user will need to be granted CREATE DIRECTORY permissions or...
The database administrators (SYS) will need to create the directory object and grant permissions to your user.

All part of tightened security. ;) We wouldn't want people writing in any old place in file system now would we; could cause the server to crash or data to be lost.

Thanks BlueShadow.

The thing is .. the DBAs will not create database directory because they rely on utl_file_dir and may not want to put their hand in something new (for a single person i.e. me).

Looking at my original message, I had mentioned the things which are in place and still I am getting this error.

Can this be because my schema does not have execute grant on UTL_FILE?

What do you mean when you say init.ora is deprecated.

If you read the second paragraph from this part of the documentation you will see that Oracle recommends to use directory objects instead of the utl_file_dir parameter. This means that it is not the recommended way of accessing files (and they may in the future remove it).

http://download.oracle.com/docs/cd/B19306_01/server.102/b25396/server.htm#sthref353

(You may want to show your DBA's this and tell them to keep up with the times. ;) )

Your original message is coming from UTL_FILE so you do have permission to execute it. What the error is saying is that there are some permission issues at the operating system level when it is attempting to write to the file, either because it can't find the directory you have specified or because the permissions on the directorie(s) don't allow it to write in that path.

:((

I asked my DBA to create a directory object which he did from sysdba user, and i changed my code accordingly but still get the same error..

That's good that you are now using directory objects.
However, as I mentioned in my last post, the cause of the error is to do with operating system permissions so those need checking to ensure that the oracle process on the server can access that directory and not just your own unix user.

I have met the same error, can any one tell me how would it happen with OS level problem, I have the permissions at the destination DIRECTORY items, and the real folders are exist with 777 mode, Does the upper folder's permissions can be the reason? it is even not share for own group. Should I set all the upper folders with 777 mode?

Oracle server process, should have the write permission for that directory. you may be aware of, in unix the permissions are granted for owner, the group and global level. Check the group of the oracle server process (ps -efwould give that) and then check the permission on the directory. if the suitable permission is given for the group on the server process belongs, then no issues.

Thanks,
Kangy

Please don't dig up 3 year old threads.

locking this one