[Chapter 22] 22.2 Application-Specific Exception Packages

22.2 Application-Specific Exception Packages

PLVexc is a very generic, low-level package. It can be used in many different applications and under many different circumstances. This flexibility is one of its key strengths. I have found, on the other hand, that for complex application development, I will usually not want to make calls directly to the PLVexc.handle procedure. Instead, I will build another exception-handling package for my application which, in turn, references PLVexc elements.

This application-specific package offers several advantages, including:

It is aware of the different kinds of data I need to report on when an error occurs. This awareness helps guarantee a consistent format for error logging; this consistency greatly aids in recovery and analysis efforts.
It can predefine additional, application-specific exceptions that have no place in PLVexc.

Does this extra layer seem like overkill? Let's explore the motivation for this additional layer of code.

22.2.1 In the Trenches with PLVexc

It is the fall of 1995. I have just built my first version of PLVexc and immediately put it to use in an application that manipulates UPC codes for a retail operation. A UPC code is made up of a 14-digit number and a description. Each UPC (product) has many attributes, such as brand, product type, price, and so on. Each attribute has a name and value.

I find that as I build my exception handlers with PLVexc.handle I need to pass the same information repeatedly to my exception log. If I am working with UPCs I want to record the UPC number and description with which I am having a problem. When manipulating attributes, I need to keep track of the problematic UPC code, attribute name, and value. I also fully intend to write recovery scripts based on my error log data. For example, if the batch-driven insert of a new UPC fails because I ran out of extents on that table, I would like to be able to reorganize the table and then execute INSERTs from the error log.

If I am going to generate INSERTs from error log text, I need to make sure that text always has the same format.

22.2.1.1 Recording consistent error data

There are two ways I can achieve the desired consistency:

Decide on a format and then send a memo to all developers working on this application. The memo explains the format (for example, "concatenate UPC code to description with a single hyphen as delimiter" and "concatenate UPC, attribute name, and value together using a single hyphen as the delimiter between these three elements") and asks developers to follow this format when using the PLVexc.handle procedure. It is then up to each person to cooperate and write the code correctly.
Provide a prebuilt procedure that encapsulates the standard format inside the program. With this second approach, a developer conforms to the standard simply by using this program.

If I adopt the first approach (which, I can assure you, is the more-traveled route) here are the kind of exception handlers I find myself writing:

WHEN OTHERS
THEN
   PLVexc.handle
      ('ins_upc', SQLCODE,
       PLVexc.c_recNstop,
       TO_CHAR (upc_in) || '-' || desc_in);
END;

and:

WHEN OTHERS
THEN
   PLVexc.handle
      ('chg_upcattr', SQLCODE,
       PLVexc.c_recNstop,
       TO_CHAR (upc_in) || '-' ||
          attr_in || '-' || new_val_in);
END;

and once, because I believe suddenly that the new format is so much clearer, I code my handler this way:

WHEN OTHERS
THEN
   PLVexc.handle
      ('ins_upc', SQLCODE,
       PLVexc.c_recNstop,
       'UPC:' || TO_CHAR (upc_in) || ' DESC:' || desc_in);
END;

22.2.1.2 Tired fingers, buggy code

This is a lot of tedious, error-prone typing. I find myself expending more brain cells remembering the format than in surmounting application obstacles. And I say to myself: "Gee, it sure would be much easier to just pass the UPC and description to the handler and let it format the data properly." That seems like such a good idea that I immediately try out the concept by recoding some exception handlers as follows:



WHEN OTHERS
THEN
   upcexc.handle
      (upcexc.c_upc_update, SQLCODE, PLVexc.c_recNstop,
       upc_in, desc_in);
END;

and:

WHEN OTHERS
THEN
   upcexc.handle
      (upcexc.c_attr_analyze,
       SQLCODE,
       PLVexc.c_recNstop,
       v_curr_upc, new_attr_in, new_val_in);
END;

where upcexc is the projected name of a new package that would know about PLVexc and the UPC application. It bridges the gap between the completely generic and the uniquely specific, using module overloading to automatically understand the types of data passed to the handle program. Example 22.1 shows the full specification of the upcexc package.

Example 22.1: The upcexc Specification

CREATE OR REPLACE PACKAGE upcexc
IS
   /* Predefined exceptions, error numbers and messages. */
   upc_update_failure EXCEPTION;
   c_errno_update_upc CONSTANT INTEGER := -20000;
   c_errmsg_update_upc CONSTANT VARCHAR2(100) := 
      'Unable to update upc with new value.';

   no_reg_center EXCEPTION;
   c_errno_no_reg_center CONSTANT INTEGER := -20003;
   c_errmsg_no_reg_center CONSTANT VARCHAR2(100) := 
      'Regional center has not been defined.'; 

   /* Contexts for exception handling. */
   c_upc_inserts CONSTANT VARCHAR2(3) := 'UI';
   c_upc_updates CONSTANT VARCHAR2(3) := 'UU';
   c_attr_analyze CONSTANT VARCHAR2(3) := 'AA';

   PROCEDURE handle
      (context_in IN VARCHAR2,
       err_code_in IN INTEGER,
       handle_action_in IN VARCHAR2,
       upc_in IN upc.upc%TYPE,
       desc_in IN upc.description%TYPE);

   PROCEDURE handle
      (context_in IN VARCHAR2,
       err_code_in IN INTEGER,
       handle_action_in IN VARCHAR2,
       upc_in IN attribute.upc%TYPE,
       attribute_in IN attribute.attribute%TYPE,
       value_in IN attribute.value%TYPE);
END upcexc;

The body of the handler for UPC errors is shown below. Notice that it does just a little bit more work than simply calling PLVexc.handle ; it formats the string passed to PLVexc.handle from the individual application-specific arguments.

PROCEDURE handle
   (context_in IN VARCHAR2,
    err_code_in IN INTEGER,
    handle_action_in IN VARCHAR2,
    upc_in IN upc.upc%TYPE,
    desc_in IN upc.description%TYPE)
IS
BEGIN
   PLVexc.handle
      (context_in, err_code_in, handle_action_in,
       'UPC=' || upc_in || ' DESC=' || desc_in);
END;

Now every time I use the upcexc.handle procedure, I am sure that my UPC and description values are formatted properly. At the same time, I use predefined constants from upcexc to specify my context or current program. I don't have to make up the name/abbreviation for the program on the fly -- it is already defined for me in the package. This additional consistency makes it easier to analyze and trace errors. Figure 22.1 shows the benefit of this additional layer of coding.

Figure 22.1: Layers of exception handling packages

The final added value of a package like upcexc is provided by the predefined application errors that fall between -20,000 and -20999. The package contains an exception, error number, and error message for each error in this range. By using these predefined elements, individual developers will not step on each others' error numbers and text.

With upcexc , I can quickly code my handlers without tripping over syntax and concatenation bars. I am, as a result, much more likely to reuse and fully leverage the underlying PLVexc package.