[Chapter 9] 9.5 Integrating PLVmsg with Error Handling

9.5 Integrating PLVmsg with Error Handling

Although PLVmsg can be used in other circumstances, PL/Vision uses it inside its exception handler package, PLVexc, and you are most likely to use it that way as well. This section shows you how to do this.

Suppose that you have taken the time to write a procedure named showerr to consolidate error handling. It accepts an error number-message combination and then both displays the message and records the error. If you do not make use of PLVmsg, a typical exception section might look like this:

EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
      showerr (SQLCODE, 'Duplicate employee name.');
   WHEN OTHERS
   THEN
      IF SQLCODE = -20200
      THEN
         showerr (-20200, 'Employee too young.');
      ELSE
         showerr (SQLCODE, SQLERRM);
      END IF;
END;

What's the problem with this approach? I can think of several drawbacks:

You have to do lots of typing. It took me several minutes to type out this example and I type quickly. It also provides lots of opportunities for errors.
The developer has to know about DUP_VAL_ON_INDEX (I, for one, always get it wrong the first time; it seems that it should be IN_INDEX).
There is some dangerous hard-coding in this section: both the -20,200 and the associated error message. What happens if you need to handle the same error in another program?

Now, suppose on the other hand that I had made use of PLVmsg. First, I would have added text to the PLVmsg repository as follows:

PLVmsg.add_text (-1, 'Duplicate employee name.');
PLVmsg.add_text (-20200, 'Employee too young.');

Sure, I had to know that ORA-00001 goes with the DUP_VAL_ON_INDEX exception, but remember that I will be writing this once for all developers on an application team. After setting these values I would also have called the norestrict toggle. This allows PLVmsg to override the usual error message for ORA-00001 with my own message.

PLVmsg.norestrict;

With the text in place and restrictions removed on accessing override messages, I can reduce my exception section from what you saw earlier to just this:

EXCEPTION
   WHEN OTHERS
   THEN
      showerr (SQLCODE, PLVmsg.text);
END;

When the SQLCODE is -1, PLVmsg.text is routed to the contents of the PL/SQL table in row -1 (and does not use SQLERRM). When SQLCODE is -20,200, the value in row -202000 is returned. Finally, for all other regular Oracle error numbers, PLVmsg obtains the text from SQLERRM.

The result is a dramatically cleaned-up exception section and an application in which all error text management is performed in one place: the PLVmsg repository.

9.5.1 Using PLVmsg in PL/Vision

As mentioned earlier, the PLVexc packages relies on PLVmsg to obtain error message text. The PLVmsg.text function is called by terminate_and_handle, which acts as a bridge between the high-level handlers, such as recNgo, and the low-level handle procedure. The implementation of terminate_and_handle is shown below:

   PROCEDURE terminate_and_handle
      (action_in IN VARCHAR2, 
       err_code_in IN INTEGER)
   IS
   BEGIN
      PLVtrc.terminate;
      handle 
         (PLVtrc.prevmod, err_code_in, action_in, 
          PLVmsg.text (err_code_in)); 
   END;

The value passed in as err_code_in might be SQLCODE, or it might be some application-specific value. Whatever its value, PLVmsg.text translates the error number into message text and passes that to the low-level handler. The handle procedure then might display this string or store it in the PL/Vision log.

By calling PLVmsg.text at this point in the exception-handling architecture, PLVexc offers its users a lot of flexibility. Suppose that when you first built your application, you didn't have time to work on error messages. You took advantage of PLVexc, but ignored completely the PLVmsg package capabilities. In this case, PLVmsg.text acted simply as a passthrough to SQLERRM. Somewhere down the line, however, you decided to enhance the error messages for your application.

To accomplish this enhancement, you would not have to change your application. All of your exception handlers that call the high-level PLVexc exception handlers are already calling PLVmsg.text. All you have to do is store all of your message text in a database table and then call PLVmsg.load_from_dbms at a good startup point for the application (in a When-New-Form-Instance trigger in an Oracle Forms-based application or in the initialization section of a common package).

From that point on (and remember: without changing any of your code!), the new error text will be used in the application.

Here are some factors to consider when working with PLVmsg:

The maximum size of a message is 2,000 bytes.
The number 100 and all negative numbers that are not between -20,000 and -20999 are considered to be Oracle error codes.
The load_from_dbms is a useful example of the kind of code you need to write to transfer data from a database table to a PL/SQL table -- even to the extent of allowing the user to specify the relevant names. You should be able to easily adapt this PLVmsg procedure to your own purposes.


9.4 The Restriction Toggle		9.6 Implementing load_ from_dbms

9.5 Integrating PLVmsg with Error Handling

9.5.1 Using PLVmsg in PL/Vision

Special Notes on PLVmsg