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;

In other words, if the INVALID_PATH exception is raised, the PLVexc program records the problem and then raises an exception to stop the process. For any other error, the SQLCODE is saved with the error information and program execution halts.

I then build the following procedure to read the first line from a file and display that line.

PROCEDURE checkitout (file_in IN VARCHAR2)
IS
   aline PLVfile.max_line%TYPE;
BEGIN
   aline := PLVfile.line (file_in, 1);
   p.l (aline);
END;

When I run the program, I keep getting unhandled exceptions. Since I do not check for any specific exceptions, the PL/SQL runtime engine simply informs me that a user-defined exception occurred. It is very difficult for me to figure out what is going on. So I paste in the exception section provided by PLVfile and then I have:

PROCEDURE checkitout (file_in IN VARCHAR2)
IS
   aline PLVfile.max_line%TYPE;
BEGIN
   aline := PLVfile.line (file_in, 1);
   p.l (aline);
EXCEPTION
   WHEN UTL_FILE.INVALID_PATH
   THEN
      PLVexc.recNstop ('Invalid path');
   WHEN OTHERS
   THEN
      PLVexc.recNstop (SQLCODE);
END;

Now when the program executes, the message "Invalid path" appears on my screen. I immediately realize that I have not called the PLVfile.set_dir to set my default directory and so my file open step is failing.

In the real world you will find yourself adding an exception section with eight different handlers so you can distinguish between all the different errors. This adds many lines to your programs, but it is worth it when you have to debug your file I/O activity. In addition, since it is predefined for you in PLVfile, you do not have to personally do a whole lot of work to get the benefit.

This standard exception section is defined in the package body within the exc_section procedure, but it is also included as a help text stub under the topic EXC_SECTION in the package specification. So you can at any time execute the following line to display the exception section:

SQL> exec PLVfile.help ('exceptions');