O'Reilly logo

Oracle PL/SQL Best Practices by Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required