UTL_FILE directory name changes oracle 19c

Question

I've scripts where UTL_FILE.FOPEN is used and the parameter passing for directory is an absolute path i.e., /asr/file/path and the corresponding oracle directory name as ASR_ABC but after up-gradation to oracle 19c the parameter is expected to be direcotry name ASR_ABC instead of absolute path /asr/file/path.

If I pass an absolute path I get error as Invalid path.

Do I have to change all the files from absolute path to directory name? or is there any work around which can be done from database to avoid changes in all files?

Answer 1

Starting with 18c, the UTL_FILE_DIR parameter no longer exists, thus the usage of directory paths instead of directory objects in UTL_FILE is no longer possible.

Desupport of UTL_FILE_DIR Initialization Parameter

Starting in Oracle Database 18c, the UTL_FILE_DIR parameter is no longer supported. Instead, specify the name of a directory object.

Even in earlier releases, the parameter and usage of paths existed only for backward compatibility.

Answer 2

You can create an Oracle DIRECTORY object named e.g. /asr/file/path and then use it just as you always did - no coding changes needed.

CREATE OR REPLACE DIRECTORY "/asr/file/path" -- quoted to preserve lower-case letters
 AS '/asr/file/path';

We needed to do this when we finally stopped using the old UTL_FILE_DIR database initialization parameter.