Posted by A couple of years back (wow…time flies!) I made a video and a post about the de-support of UTL_FILE_DIR in 18c. This was good thing because the number of people opting for “utl_file_dir=*” in their init.ora file, and hence opening themselves up to all sorts of risks seemed to be large! (The post elaborates on this more with an example of erasing your database with a short UTL_FILE script 
)
But a friend came to me with a valid rebuttal, namely that while most people tended to use UTL_FILE_DIR to read/write files to a small list of directories (most typically one), other customers had taken advantage of UTL_FILE to utilise complex directory structures with hundreds or even thousands of sub-folders. Converting that infrastructure (which is trivially implemented with “utl_file_dir=*” even with all its risks) is a tougher proposition when using directory objects in the database, because a single directory object points to a single directory.
To assist with moving away from utl_file_dir in such a circumstance, I’ve whipped up a small utility which aims to be a drop-in replacement for your UTL_FILE.FOPEN calls. The intent of the utility is that moving to the new directory objects method should simply be the case of adding a nested call. Thus if you had an existing file open call of:
f utl_file.fopen('/u01/app/oracle','myfile.dat',...);
you simply nest the utility function as follows:
f utl_file.fopen( utl_file_dir('/u01/app/oracle'),'myfile.dat',...);
and you should be good to go.
The way this is achieved is that the utility does its best to come up with a sensible but unique name for a directory object to map to the standard OS path that has been passed. The comments in the code explain the workings, and of course you’re free to modify it to suit your needs.
create or replace
function utl_file_dir(p_file_or_dir varchar2,
p_create_dir boolean default true,
p_add_hash boolean default true) return varchar2 is
pragma autonomous_transaction;
l_default_dir varchar2(128) := 'TEMP';
l_delim varchar2(1) := '/'; -- change for Windows
l_last_delim int := instr(p_file_or_dir,l_delim,-1);
l_file varchar2(255);
l_dir_path varchar2(255);
l_dir_object varchar2(255);
l_exists int;
l_clash int;
begin
if l_last_delim = 0 then
--
-- If no presence of a directory delimiter, then we assume the entire
-- string is a file if it contains a '.' (ie, a file extension)
-- and return a default directory object name (l_default_dir)
-- otherwise we assume the string is a directory in its own right
--
if p_file_or_dir like '%.%' then
l_dir_object := l_default_dir;
else
l_dir_path := p_file_or_dir;
end if;
else
--
-- We have a delimiter. The directory is the contents up to
-- the last delimiter, unless there is no file extension past
-- that last delimiter. In that latter case, we assume the entire
-- string is a directory in its own right
--
l_file := substr(p_file_or_dir,l_last_delim+1);
if l_file like '%.%' then
l_dir_path := substr(p_file_or_dir,1,l_last_delim-1);
else
l_dir_path := p_file_or_dir;
end if;
end if;
--
-- Now we make a clean directory object name from the path. We could
-- of course use any random string, but this is designed to make things
-- a little more intuitive.
--
-- For example '/u01/app/oracle' will become U01_APP_ORACLE
--
-- You have a choice here in terms of the risk element of collisions depending
-- on how loose your folder structure is. For example, the two paths:
--
-- /u01/app/oracle/
-- /u01/app/"oracle-"/
--
-- by default will map to the same clean name of U01_APP_ORACLE and we will
-- report an error in this instance.
--
-- Alternatively (and the default) is that we take our directory path and
-- grab the last few bytes from a MD5 hash on it, to greatly increase the likelihood
-- of a non-clashing directory name. In the above example, the clean directory names become
--
-- U01_APP_ORACLE_25B9C47A
-- U01_APP_ORACLE_7D51D324
--
-- So what you lose in intuitive readability you gain in reduced chance of collision.
-- This is controlled with "p_add_hash"
--
if l_dir_object is null then
l_dir_object := regexp_replace(replace(replace(l_dir_path,l_delim,'_'),'-','_'),'[^[:alnum:] _]');
l_dir_object := regexp_replace(trim('_' from upper(regexp_replace(l_dir_object,' *','_'))),'__*','_');
if p_add_hash then
select substr(l_dir_object,1,119)||'_'||substr(standard_hash(l_dir_path,'MD5'),1,8)
into l_dir_object
from dual;
else
l_dir_object := substr(l_dir_object,1,128);
end if;
end if;
-- Now we go ahead and create that directory on the database.
-- The user running this function must have CREATE ANY DIRECTORY privilege granted
-- explicitly, which means of course, you should protect this routine and perhaps add
-- some sanity checking to make sure that no-one creates a directory to reference (say)
-- the objects in V$DATAFILE !
if p_create_dir then
select count(*),
count(case when directory_path != l_dir_path then 1 end)
into l_exists,
l_clash
from all_directories
where directory_name = l_dir_object;
if l_exists = 0 then
execute immediate 'create directory "'||l_dir_object||'" as q''{'||l_dir_path||'}''';
else
--
-- If (hash or not) we enter the nasty situation where the same clean name would
-- map to 2 path names, we give up and go home.
--
if l_clash > 0 then
raise_application_error(-20000,'Found matching directory object '||l_dir_object||' with different path from >'||l_dir_path||'<');
end if;
end if;
end if;
commit;
return l_dir_object;
end;
/
sho err
Here are some examples of typical usage. By default we would actually create the directory object, but you can override this for simple testing like I’ve done below
SQL> -- Examples
SQL>
SQL> variable dirname varchar2(128)
SQL>
SQL> -- standard file
SQL> exec :dirname := utl_file_dir('/u01/app/oracle/test.dat',p_create_dir=>false)
PL/SQL procedure successfully completed.
SQL> print dirname
DIRNAME
-----------------------------------------------------------------------------------------------
U01_APP_ORACLE_9E2472BB
SQL>
SQL> -- quoted/spaces etc
SQL> exec :dirname := utl_file_dir('/u01/"asd app"/oracle/test.dat',p_create_dir=>false)
PL/SQL procedure successfully completed.
SQL> print dirname
DIRNAME
-----------------------------------------------------------------------------------------------
U01_ASD_APP_ORACLE_FFDC5BEA
SQL>
SQL> -- trailing delimiter.
SQL> exec :dirname := utl_file_dir('/u01/app/oracle/',p_create_dir=>false)
PL/SQL procedure successfully completed.
SQL> print dirname
DIRNAME
-----------------------------------------------------------------------------------------------
U01_APP_ORACLE_25B9C47A
SQL> exec :dirname := utl_file_dir('/u01/app/oracle--/',p_create_dir=>false)
PL/SQL procedure successfully completed.
SQL> print dirname
DIRNAME
-----------------------------------------------------------------------------------------------
U01_APP_ORACLE_7D51D324
SQL>
SQL> -- no file
SQL> exec :dirname := utl_file_dir('/u01/app/oracle',p_create_dir=>false)
PL/SQL procedure successfully completed.
SQL> print dirname
DIRNAME
-----------------------------------------------------------------------------------------------
U01_APP_ORACLE_9E2472BB
SQL>
SQL> -- no delimiter
SQL> exec :dirname := utl_file_dir('mydir',p_create_dir=>false)
PL/SQL procedure successfully completed.
SQL> print dirname
DIRNAME
-----------------------------------------------------------------------------------------------
MYDIR_33CD7707
SQL>
SQL> -- no delimiter but probably a file
SQL> exec :dirname := utl_file_dir('mydir.txt',p_create_dir=>false)
PL/SQL procedure successfully completed.
SQL> print dirname
DIRNAME
-----------------------------------------------------------------------------------------------
TEMP
TL;DR: This utility lets moving away from UTL_FILE_DIR be less difficult.
Learning is not a spectator sport 