|
Chapter 9 PLVmsg: Single-Sourcing PL/SQL Message Text |
|
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.
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.