Advanced Oracle PL/SQL Programming with Packages

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