1

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.

Refael
  • 6,753
  • 9
  • 35
  • 54

1 Answers1

4

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/

Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69
  • But you must create the directory first. – Refael Sep 17 '14 at 21:27
  • Yes @Refael, the theme is only DBA created directories can be accessed. I remember, we had to change 100+ scripts to make this change happen, when we migrated from 10g to 11g.. We wrote a function to return the directory object, else default directory if one does not exists. – Maheswaran Ravisankar Sep 17 '14 at 21:27