I wrote a blog post called The Death of UTL_FILE which attracted a comment from a reader:
“There is NO chance to stay at UTL_FILE as it is DESUPPORTED starting with database Version 18c”
This is not the case, but since I wanted to clarify what has changed in 18c, it warrants this small but separate blog post. When UTL_FILE first into existence in Oracle 7, the concept of directory object did not apply to UTL_FILE. Clearly we could not just let UTL_FILE to write to any destination, otherwise a malicious person could write a little PL/SQL block like this:
declare f utl_file.file_type; begin for i in ( select regexp_substr(name,'(.*)\\(.*)', 1, 1, 'i', 1) path, regexp_substr(name,'(.*)\\(.*)', 1, 1, 'i', 2) name from v$datafile order by file# desc ) loop f := utl_file.fopen(i.path,i.name,'W'); end loop; end; utl_file.fclose_all; end; /
and voila! No more database. So an initialization parameter was created to nominate which directories UTL_FILE was allowed to access. This parameter was called UTL_FILE_DIR, and the above code hopefully is justification enough to show that you should never ever set UTL_FILE_DIR to “*”, meaning it could write to wherever the OS permissions on the Oracle software account would allow it. Bye Bye datafiles…bye bye database
In more recent versions, UTL_FILE was improved so that the directory parameter could be supplied as a directory object. This is a much tighter implementation because read and write privileges on directory objects can be controlled from within the database.
In 18c, it is not UTL_FILE that has been de-supported, it is the ability to use the older style convention of hard-coded path names for the directory that is no longer allowed. You have to use directory objects. If you set the traditional UTL_FILE_DIR path in the spfile it will be ignored, and you’ll get a warning on startup.
SQL> alter system set utl_file_dir = 'c:\temp' scope=spfile; System altered. SQL> startup force ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. -- -- alert log -- Obsolete system parameters with specified values: utl_file_dir End of obsolete system parameter listing
But even if you set UTL_FILE_DIR in the spfile, you will not be allowed to use OS directory paths in the UTL_FILE dir calls.
SQL> declare 2 f utl_file.file_type; 3 begin 4 f := utl_file.fopen('c:\temp','demo.dat','W'); 5 utl_file.fclose_all; 6 end; 7 / declare * ERROR at line 1: ORA-29280: invalid directory object ORA-06512: at "SYS.UTL_FILE", line 41 ORA-06512: at "SYS.UTL_FILE", line 478 ORA-06512: at line 4
If you prefer a video version of this explanation, I had some fun riding my bike when I talked about this when 18c first came out earlier this year.