Hello,

I have written a SP to store a GIF in an Oracle BLOB column.

When I execute the SP, the returned error is ORA-22285: non existing directory or file for FILEEXISTS.

In online documentation Ihave seen the statement

CREATE OR REPLACE DIRECTORY 'alias' as 'full directory'

How can CREATE OR REPLACE DIRECTORY be used in a SP?

Kind regards,
Ronan van Riet

Mr. Ronan,
Your query is not clear to me. Though try to get something from following lines.

To store your gif you must have to specify a file name and the path where it resides..right...So to specify a directory Oracle 9.1 supports "CREATE OR REPLACE DIRECTORY AS DIRECTOY NAME" command, but if u use 8.1.7 you dont have this facility as per my knowledge. Moreover u need to edit init.ora file and add an entry like this at the end:

utl_file_dir=* or
utl_file_dir=The directory where your gif exists

Then u need to restart the instance.
Or if you wanna use this "create directory" statement in SP u must have to go for oracle9.1.0 or higher. OR
Type the command at sql prompt..so that dir wil b created and give proper permissions..

Hope it cud help you....

Agreed. Please provide more information. However, one of the most common problems I've found when doing BFILE or BLOB stuff, is that the directory specification in your BLOB access function is not capitalized. For instance:

Good luck.

JoeB