Never explicitly reference Oracle system error codes

Question:
I was recently given responsibility to make a change to a program built several years ago by a consultant. She used FORALL to perform a massive set of inserts, and included the SAVE EXCEPTIONS clause so we could as many of the inserts completed as possible. That all made sense to me. But then I looked at the exception section and got really confused. It contained this code:
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE - 24381
THEN
...
I was able to figure out what was going on—after a while. But I'd really like to change it so that the next person working on this code doesn't have to do the same detective work. What should I do with this code?—Josef
Answer:
I congratulate you on your aversion to this sort of code!
If you are like me, as you write code or look at someone else's code, a little voice often pipes up inside your brain saying, "That's a nasty way to do things. Why are you doing that?" or "You're going quick-and-dirty again. You know you're going to pay for that later."
It is so, so important to listen to that voice, to stop and make the time to "do things right." Then you just have to know what "right" is.
In the case of the mysterious "-24381," first the reason for that code and then a suggestion on how to fix it:
When you include the SAVE EXCEPTIONS clause in your FORALL statement, you are asking Oracle to save information about any exceptions that are raised while executing the DML statements specified in the FORALL.
After all rows have been processed, if at least one exception was raised, then Oracle raises the generic "bulk exception" error, which has the error code -24381, but has no name associated with it. Therefore, it would seem that the only way to handle this exception is to write code like that found in your program:
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE - 24381
THEN
...
One problem with this code is that I am handling a specific, anticipated exception within WHEN OTHERS. This is generally a bad idea. If you know an exception might be raised, you should have a separate WHEN section for that error.
The bigger problem with this exception section, however, is that anyone coming along later will find the code quite intimidating. "What is that number?" the developer wonders, "And why did the original coder know about this number, but I am ignorant? I feel stupid. I don't feel equipped to change this code. I am afraid to change this code."
That is a bad way to write code. Instead, you should construct your programs so that they are welcoming, so that a person feels like they are at home in your code, might have written it themselves, and are comfortable making changes.
One way to fix this code is to add a comment:
EXCEPTION
WHEN OTHERS
THEN
/* This is the error raised by FORALL when you include
SAVE EXCEPTIONS and at least one error is encountered. */
IF SQLCODE - 24381
THEN
...
I have problems with this approach, namely:
• It takes a while to write the comment, cutting into time badly needed for development;
• I am not sure that comments in code are the right place to educate people about language features;
• You will have to write this comment over and over again, in each exception section where this code is needed.
The best way to clean up this code and make it less intimidating is to do what Oracle could have done (should have done?): give that error a name.
Suppose that I create a package as follows:
CREATE OR REPLACE PACKAGE common_oracle_errors
IS
e_bulk_error_encountered EXCEPTION;
PRAGMA EXCEPTION_INIT (bulk_error_encountered, -24381 );
END common_oracle_errors;
/
I can then change the exception section to:
EXCEPTION
WHEN common_oracle_errors.e_bulk_error_encountered
THEN
...
WHEN OTHERS
THEN
...
Now the code is self-documenting, and I have created an exception that can be used throughout my entire application. I also have a place to give names to other exceptions I encounter, but for which Oracle does not provide a name. I might, for example, add a section of exceptions for dynamic SQL or for DML-related errors, like this:
CREATE OR REPLACE PACKAGE common_oracle_errors
IS
e_bulk_error_encountered EXCEPTION;
PRAGMA EXCEPTION_INIT (bulk_error_encountered, -24381 );

e_null_column_value EXCEPTION;
PRAGMA EXCEPTION_INIT (e_null_column_value, -1400);

e_existing_fky_reference EXCEPTION;
PRAGMA EXCEPTION_INIT (e_existing_fky_reference, -2266);

e_check_constraint_failure EXCEPTION;
PRAGMA EXCEPTION_INIT (e_check_constraint_failure, -2290);

e_no_parent_key EXCEPTION;
PRAGMA EXCEPTION_INIT (e_no_parent_key, -2291);

e_child_record_found EXCEPTION;
PRAGMA EXCEPTION_INIT (e_child_record_found, -2292);
END common_oracle_errors;
/