#

| Chapter 9 PLVmsg: Single-Sourcing PL/SQL Message Text |

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

9.4 The Restriction Toggle |  | 9.6 Implementing load_ from_dbms

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.