utl_file_dir parameter was deprecated at least in 12c and completely removed at least in 19c.
For UTL_FILE procedures like fopen there are 2 different ways to specify directory:
- Using Oracle DIRECTORY object
- Using string with full path. This approach work if and only if the specified directory is present in the parameter UTL_FILE_DIR, otherwise it raises ORA-29280 (see example below). This way is not working in 19c as UTL_FILE_DIR does not exists anymore
The second approach is work regardless UTL_FILE_DIR and this is the only way in Oracle 19c
SELECT NAME, VALUE FROM v$parameter WHERE NAME = 'utl_file_dir';
NAME VALUE
utl_file_dir c:\temp\utl_file_dir
======================================================
Using folder name with full path.
UTL_FILE_DIR does NOT contain folder - NOT WORKING!!!
======================================================
DECLARE
v_fp sys.utl_file.file_type;
BEGIN
v_fp := sys.utl_file.fopen('c:\temp\non_utl_file_dir', 'test_file.csv', 'w', 32000);
utl_file.fclose(v_fp);
END;
/
DECLARE
v_fp sys.utl_file.file_type;
BEGIN
v_fp := sys.utl_file.fopen('c:\temp\non_utl_file_dir', 'test_file.csv', 'w', 32000);
utl_file.fclose(v_fp);
END;
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 4
======================================================
USING DIRECTORY OBJECT - WORKING
======================================================
CREATE OR REPLACE directory REPORT_DIRECTORY AS 'c:\temp\non_utl_file_dir';
Directory created
DECLARE
v_fp sys.utl_file.file_type;
BEGIN
v_fp := sys.utl_file.fopen('REPORT_DIRECTORY', 'test_file.csv', 'w',32000);
utl_file.fclose(v_fp);
END;
/
PL/SQL procedure successfully completed
======================================================
Using folder name with full path.
UTL_FILE_DIR contains folder - WORKING
======================================================
DECLARE
v_fp sys.utl_file.file_type;
BEGIN
v_fp := sys.utl_file.fopen('c:\temp\utl_file_dir', 'test_file.csv', 'w',32000);
utl_file.fclose(v_fp);
END;
/
PL/SQL procedure successfully completed