The application handles and logs errors the same way throughout

the positive side of the integer range. While it is possible that Oracle will, over time, use other positive numbers, it is very unlikely. That leaves an awful lot of error codes for us to use.

I took this approach when designing the , a freeware error management utility. With the Quest Error Manager, you can define your own errors in a special repository table. You can define an error by name and/or error code. The error codes can be negative or positive. If the error code is positive, then when you raise that exception, QEM uses RAISE_APPLICATION_ERROR to raise a generic exception (usually −20,000). The information about the current application error code is embed‐ded in the error message, which can then be decoded by the receiving program.

EXCEPTION

WHEN NO_DATA_FOUND

INSERT INTO errlog VALUES

(v_err,v_msg,v_prog,SYSDATE,USER);

v_prog := 'fixdebt';

INSERT INTO errlog VALUES

So what’s wrong with all that? The mere fact that I can actually explain everything that is going on is an indication of the problem. I have exposed and hardcoded all the steps I take to get the job done. The result is that (1) I write a lot of code, and (2) if anything changes, I have to change a lot of code. Just to give you one example, notice that I am writing to a database table for my log. This means that the log entry has become a part of my logical transaction. If I need to roll back that transaction, I lose my error log.

There are several ways to correct this problem—for example, I could write to a file or use autonomous transactions to save my error log without affecting my main transac‐tion. The problem is that, with the way I have written the preceding code, I have to apply my correction in potentially hundreds of different programs.

errpkg.record_and_continue (

SQLCODE, 'No company for id ' || TO_CHAR (v_id));

My error-handling package hides all the implementation details; I simply decide which of the handler procedures I want to use by viewing the specification of the package. If I want to record the error and then continue, I call record_and_continue. If I want to record and then stop, clearly I want to use record_and_stop. How does it record the error? How does it stop the enclosing block (i.e., how does it propagate the exception)? I don’t know, and I don’t care. Whatever it does, it does it according to the standards defined for my application.

All I know is that I can now spend more time building the interesting elements of my application, rather than worrying over the tedious, low-level administrivia.