Oracle PL/SQL Best Practices

Name

BIP-03: Validate the setup of UTL_FILE with simple tests.

Synopsis

The hardest part about using UTL_FILE is to get it up and running. You must add one or more UTL_FILE_DIR entries in your initialization parameter file, and then restart your database to have those changes take effect.

The UTL_FILE_DIR parameter specifies those directories in which UTL_FILE can operate. The format of the parameter for file access in the INIT.ORA file is:

utl_file_dir = directory

Include a parameter for UTL_FILE_DIR for each directory you want to make accessible for UTL_FILE operations. The following entries, for example, enable four different directories in Unix:

utl_file_dir = /tmp
utl_file_dir = /ora_apps/hr/time_reporting
utl_file_dir = /ora_apps/hr/time_reporting/log
utl_file_dir = /users/test_area

To bypass server security and allow read/write access to all directories, you can use this special syntax:

utl_file_dir = *

Don’t use this option on production systems. In a development system, this entry certainly makes it easier for developers to get up and running on UTL_FILE and test their code (but it also allows them to write “Long Live PL/SQL!” on top of your database control files!). You should, however, allow access only to a few specific directories when you move the application to production.

Here are some observations on working with and setting up accessible directories with UTL_FILE:

• Access isn’t recursive through subdirectories. If the following lines were in your INIT.ORA file, for example: ...