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.