Advanced Oracle PL/SQL Programming with Packages

13.9. Handling File Errors with PLVfile

Many different errors can occur when you are attempting to read and write files. A file may not have been opened when you try to read it, or you may have opened it in read-only status and tried to write to it, and so on. When the PL/SQL runtime engine detects a problem, it raises one of the system exceptions (NO_DATA_FOUND and VALUE_ERROR) or one of the package-specific exceptions (such as UTL_FILE.READ_ERROR). The package-specific exceptions are user-defined, which means that if you check the SQLCODE return value in a WHEN OTHERS exception section, you see the value 1. This information is not very useful for debugging purposes.

The current version of PLVfile does attempt to trap the appropriate UTL_FILE exceptions in some of its programs (see the implementations of fcreate, fopen, and get_line, among others). It also provides a sample program which has an exception section handling each of the package-specific exceptions.

This procedure, exc_section , is not intended to be executed as it is written. Instead, you should cut the exception section from this procedure and paste it into your own program. You will then be able to trap, identify, and respond to the specific errors raised when using the UTL_FILE package.

To show how to use this block of code, suppose that the exception section consists only of these lines:

EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN PLVexc.recNstop ('Invalid path'); WHEN OTHERS THEN PLVexc.recNstop (SQLCODE); END; ...