utl_file fopen - I'm unable to to write text to a file

Question

Recently, I updated a version for DB, to Oracle 11G. From that moment, writing to a file fails.

My code:

UTL_FILE.FOPEN('/tmp', 'file.txt', 'w');

I got "ora-29280 invalid directory path"

I know I should use with Oracle DIRECTORY ("create directory..."), but in Oracle 10G it worked great with an explicit path, and I dont want to change my code.

Thanks.

Answer 1

No you can't.. The Document says it!

The workaround could be using ALL_DIRECTORIES for the /tmp and then use it in FOPEN

SELECT DIRECTORY_NAME INTO MY_DIR FROM ALL_DIRECTORIES WHERE DIRECTORY_PATH='/tmp';

UTL_FILE.FOPEN(MY_DIR, 'file.txt', 'w');

It seems Oracle no more supports the file path directly, for security concerns.

The directory has to be created properly before hand.

CREATE OR REPLACE DIRECTORY TEMP AS '/tmp'
/

GRANT READ, WRITE ON DIRECTORY TEMP TO MYUSER
/

You can write a function to return the requested DB Directory name for the full path requested. And return the default directory name in case of unavailability.

EDIT: UTL_FILE_DIR can be used for hard coded Directory paths. Provided the INIT.ORA parameter had those directory paths. Else, it have to be added, and the database has to be bounced.

The below link has some useful info on how to use it, and Pros of using the Directory objects instead of hard coded paths!

http://www.otnblogs.com/using-oracle-directories-instead-utl_file_dir/