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:
Does this extra layer seem like overkill? Let's explore the motivation for this additional layer of code.
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.
18.104.22.168 Recording consistent error data
There are two ways I can achieve the desired consistency:
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;
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;
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_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.
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.
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.