Oracle PL/SQL Best Practices

Name

BIP-04: Handle expected and named exceptions when performing file I/O.

Synopsis

You may encounter a number of difficulties (and therefore exceptions) when working with operating system files. The UTL_FILE package itself offers a set of named exceptions that are specific to the package, such as UTL_FILE.INVALID_OPERATION. (The UTL_FILE.GET_LINE procedure can also raise the standard NO_DATA_FOUND exception.) These named exceptions are all user-defined exceptions, which means that the SQLCODE is the same for all the exceptions: +1. For this reason, you must handle UTL_FILE exceptions by name, or you won’t be able to determine which error was raised.

Every block of code that works with UTL_FILE should therefore have an exception section that: (a) traps each UTL_FILE exception by name, (b) “translates” the exception into a string that can be displayed so you can tell which error was raised, and (c) closes any opened files.

Example

The best way to do this is to build a “local procedure” that displays error information and closes the file, as shown here:

IS fid UTL_FILE.FILE_TYPE; PROCEDURE recNgo (str IN VARCHAR2) IS BEGIN pl ('UTL_FILE error: ' || str); UTL_FILE.FCLOSE (fid); END; BEGIN ... your code EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN recNgo ('invalid_path'); WHEN UTL_FILE.INVALID_MODE THEN recNgo ('invalid_mode'); WHEN UTL_FILE.INVALID_FILEHANDLE THEN recNgo ('invalid_filehandle'); WHEN UTL_FILE.INVALID_OPERATION THEN recNgo ('invalid_operation'); WHEN UTL_FILE.READ_ERROR THEN ...