Skip to Main Content
Advanced Oracle PL/SQL Programming with Packages
book

Advanced Oracle PL/SQL Programming with Packages

by Steven Feuerstein
October 1996
Intermediate to advanced content levelIntermediate to advanced
687 pages
16h 41m
English
O'Reilly Media, Inc.
Content preview from 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin

Publisher Resources

ISBN: 1565922387Supplemental ContentCatalog PageErrata