Oracle PL/SQL Best Practices

Name

BIP-02: Improve the functionality and error handling of UTL_FILE by using a comprehensive encapsulation package.

Synopsis

UTL_FILE offers only the most primitive file I/O capabilities and leaves much to be desired. Here’s a list of some of the things you can’t do:

• Delete a file

• Obtain or change the privileges on a file

• Read or write a random line in a file (sequential operations only)

• Obtain information about directories (files in a directory, whether or not a name indicates a file or a directory, etc.)

• Define a path for finding and opening files

In addition, the way that UTL_FILE raises exceptions can make it difficult to identify and resolve file-handling errors (see [BIP-04: Handle expected and named exceptions when performing file I/O.] for details on this issue).

Ah well, we’ve just got to make do with what Oracle gives us, right? Wrong! You should instead create your own (or take advantage of someone else’s) package that sits on top of UTL_FILE and enhances its functionality. If you don’t want to go to the trouble of implementing an entire “replacement” package, you can also create alternatives to individual programs, as I demonstrate in [BIP-05: Encapsulate UTL_FILE.GET_LINE to avoid propagating the NO_DATA_FOUND exception.].

Example

You can find one example of an encapsulation for UTL_FILE in the RevealNet Active PL/SQL Knowledge Base. This package, called PLVfile (PL/Vision file management), is implemented entirely in PL/SQL and so inherits some of the limitations of UTL_FILE. ...