Name

BIP-06: Soft-code directory names in your calls to UTL_FILE.FOPEN.

Synopsis

Oracle requires that you pass the directory name along with the filename when you open a file. The tendency among developers is to place these directory names directly in the call to UTL_FILE.FOPEN, thinking that the locations of files will not change or not really envisioning an alternative. A directory name is just one example of an operating system dependency within PL/SQL code, and you should make every effort to isolate such dependencies from your business logic.

There are several distinct approaches to avoiding such hard-coding:

  • Store directory names in a database table. Instead of calling UTL_FILE.FOPEN directly, call your own file open function that obtains the directory from the table, based on various characteristics, such as instance name, development phase, application component, etc.

  • Obtain the current settings for UTL_FILE_DIR (the allowable directories for read/write activity) and then extract your directory from that string. This is possible if you can identify the needed directory from its name.

  • Add support for a path in UTL_FILE, in which you define a list of directories from which a file may be read. Again, provide your own encapsulation of UTL_FILE.FOPEN that reads from the path list instead of a static, single directory.

The following example demonstrates each technique.

Example

First, let’s take a look at “soft coding” directory names in a database table (I will not show all the code ...

Get Oracle PL/SQL Best Practices now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.