Search code examples
oracleoracle10gdirectoryutl-file

UTL_FILE, UTL_FILE_DIR and DIRECTORY, how do they affect UTL_FILE.FREMOVE?

FREMOVE is failing with an error "ORA-29280: invalid directory path"

It works fine when I add the path to the UTL_FILE_DIR and restart the database.

This is regardless of having the directory as an Oracle directory with both READ and WRITE granted.

Solution

  • When using UTL_FILE you have one of two options:

    Option 1: UTL_FILE_DIR must have the directory you want to use in the parameter. If it's not there it will not work. The downside is that anytime you need this parameter changed or added to you need to bounce the database. With this method the first parameter of UTL_FILE.FREMOVE will be passed the actual OS directory.

    Option 2: you alternatively use an oracle Directory object. You would use the directory object name (not the actual OS directory) in the first parameter of UTL_FILE.FREMOVE

    References:

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm

    http://www.sc.ehu.es/siwebso/KZCC/Oracle_10g_Documentacion/server.101/b10755/initparams223.htm