UTL_FILE_DIR and 18c revisited

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 Smile)

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.

Code at https://github.com/connormcd/misc-scripts

 

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.