UTL_FILE_DIR in Oracle

UTL_FILE_DIR parameter allow you to specify one or more directories where you can do input/output operations in PL SQL.These file can be written and can be read using UTL_FILE Package.

syntax for setting UTL_FILE_DIR is

alter system set UTL_FILE_DIR = '/tmp' scope=spfile;

Specifying multiple directories in the spfile


alter system set utl_file_dir='/tmp/dir1','/tmp/dir2','/tmp' scope=spfile

In the past(upto Oracle 8i), accessible directories for the UTL_FILE functions were specified in the initialization file using the UTL_FILE_DIR parameter. The disadvantage of the init param is that if it is used, it is valid for all USERS in the database. There is no way (other than totally disable utl_file for someone) to selectively restrict directories to someone.

From Oracle 9i, UTL_FILE_DIR access is not recommended. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIR. Directory objects offer more flexibility and control to the UTL_FILE access.Moreover directories can be maintained dynamically (that is, without shutting down the database), and are consistent with other Oracle tools. CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default.

Written by admin on January 28th, 2011 with comments disabled.
Read more articles on Oracle Initialization Parameters.

Comments disabled

Comments on this article have been disabled.