Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 9.4 The Restriction ToggleChapter 9
PLVmsg: Single-Sourcing PL/SQL Message Text
Next: 9.6 Implementing load_  from_dbms
 

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:

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.


Previous: 9.4 The Restriction ToggleAdvanced Oracle PL/SQL Programming with PackagesNext: 9.6 Implementing load_  from_dbms
9.4 The Restriction ToggleBook Index9.6 Implementing load_ from_dbms

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference