Oracle Support

This article explains how to use Oracle Directories instead of using UTL_FILE_DIR parameter to read & write files through database PLSQL packages, which is security best practices for database and also provide flexibility to change underlying OS directory structure without changing your code & DB bounce. I will suggest all development team members to migrate from UTL_FILE_DIR to Oracle Directories.

UTL_FILE_DIR

UTL_FILE_DIR lets you specify one or more directories that Oracle should use for PL/SQL file I/O. If you are specifying multiple directories, you must repeat the UTL_FILE_DIR parameter for each directory on separate lines of the initialization parameter file. All users can read or write to all files specified by this parameter. Therefore all PL/SQL users must be trusted with the information in the directories specified by this parameter. Also if you need to remove/add any directory, DBA has to bounce whole database.

Oracle Directories (DBA_DIRECTORIES)

Directories are Oracle objects you also use similarly like UTL_FILE_DIR with more flexibility and security. You must have used it when working with Data Pump (expdp / impdp). DBA_DIRECTORIES describes all directory objects in the database. 

Benefits of using Oracle Directories

There are multiple benefits of using Oracle Directories over UTL_FILE_DIR.

• You can GRANT read & write access at granular level to only user who will be using this in PLSQL programs.
• Developer don’t need to change their code if underlying OS directory structure change because of any reason.
• Changes to directories are dynamic and not required to bounce the database.