Error handling with PL/SQL

The information can be divided into three categories (what, where and why):

• The what is about the error itself. What kind of error occurred, what is the error message.
• The where is about the program units involved in raising the error.
• The why is about the data that was processed when the error occurred.

Gathering this information is one of the primary goals of error handling.

Solution Design

Now that the objective is known, we need to create a design that will meet these objectives.

The data will be kept in a collection. We use encapsulation to hide the actual variable from the “outside world”. There are stored procedures that give controled access to the variable: add_detail and clear_details. Using a collection also allows us to keep data just in case of an error. When there is no error, it can be trown away. This is also usefull when there is more than 1 exception handler and each exception must store the same information.

De data will be stored in 2 tables, a master table “LOGS” and a detail table “LOG_DETAILS”. This will provide an easy insight in the number of errors: one row in table LOGS for each error and any number of rows in LOG_DETAILS. One procedure is doing the task of storing both the log and the log_details.

The datatype of the value that is stored is varchar2 so that any datatype will fit in. Additionally an attribute NAME can be stored, to form a name value pair with the VALUE. So you can store the column name together with the value at the time of the error. The program_unit attribute can hold the name of the procedure where the error occurs.
The LOGS table is generic and can also hold other types of log entries besides errors.

Example 1

The example 1 demo program loops through the table EMP and adds 10 to the salary of each employee. This is done by calling a procedure from package emp_demo. When the empno ends with 00, an error is caused by division by zero. Only one row in EMP meets this condition so we expect only one error.

Output in Table LOGS

Discussion

1. When you review the demo package and the example scripts you may notice that the exception handlers contain only 3 different statements or actions: add_detail, raise and save. In a real world environment there will be other requirements as well, for example send a mail. These additional actions should be described in a design document. It is not the responsiblity of a developer to define what actions are appropriate. The complete list of actions to be considered for any exception handler should be in the design document. A definition of error handling from a developers point of view is: for each error select and apply the appropriate actions.
2. How many exception handlers do you need then? The answer is: one for each unique combination of actions. As demonstrated in example 2 there is no need to define an exception handler for each different error. When the actions are the same the error can be handled by the same exception handler.
3. The process of defining your error handlers can be simplified by distinguishing primary and secundary exception handlers. Secundary ones are those like in the error_handling_demo package: procedures that are never called directly but always by another procedure. The exception handler can be standard: add_detail(s) and reraise the error.
4. The rest defines the primary category. This is where the real choices have to be made.
   One of the additional benefits from this method of error handling, is that it works great for debugging. When applied directly during the development process, it will help you find the bugs more quickly.