22.2.1.1 Recording consistent error data

There are two ways I can achieve the desired consistency:

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

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

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.