When to use Oracle's "when others"

Maybe it's been a while since you've had to write a procedure or function in Oracle, and you have to dust off your recollection how to handle exceptions. Exception handling code, in any language, is by its non-linear nature, weird, after all.

Let's say you've been given an Oracle package, and you see lots of functions and procedures that end with

  
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Failure during PROC_NAME. Processing halted unexpectedly. ID: '
                         || lv_id || '. ' || SQLERRM);

END PROC_NAME;

Given that someone spent the time to write this, you are likely to presume the exception block is fine. But in the back of your mind, there's something you recall that's dangerous about writing exceptions, something you are supposed to be sure to do. But what is it?

Tom Kyte at asktom.oracle.com has this to say about it.

A when others is almost always a BUG unless it is immediately followed by a RAISE.

The point of an exception block is to catch exceptional conditions you are EXPECTING, handle them gracefully and continue.

You should only catch the exceptions you are expecting and can do something about. Let the others propagate out so you can detect them (so you see them)

and

I truly wish we didn't even support WHEN OTHERS.

When asked to evaluate this example:

Exception

when others then
raise_application_error( -20001,'Error Encountered'||sqlcode||'-'||sqlerrm)

Tom said simply that it shouldn't have been written, that it doesn't do anything that isn't already being done, so writing it just confuses the next person who presumes its a necessary and beneficial step.

Tom's preference is, "if the error is going to be propagated BACK to the client to catch the USER DEFINED EXCEPTIONS by name and use raise application error to turn them into something MEANINGFUL for the client:"

exception
when utl_file.invalid_path then
raise_application_error(-20001,
'INVALID_PATH: File location or filename was invalid.');
when utl_file.invalid_mode then
raise_application_error(-20002,
'INVALID_MODE: The open_mode parameter in FOPEN was invalid.');

...

end;

as opposed to

EXCEPTION
WHEN OTHERS THEN
ls_errmess := SUBSTR(SQLERRM,1,200);
raise_application_error(-20007, ls_errmess);
end;

This would be better because it reraises the exception so the client has a clue what happened:

EXCEPTION
WHEN OTHERS THEN
smccarthy.excep.log_errors (errlocation => 'TEST.T1_AUDIT_UPD_DEL');
raise;
END;

When to include exception handing

because when you catch an exception -- it is expected that

a) you were expecting it
b) you can deal with it
c) the reason you caught it is because A and B were true, so the exception that
you caught isn't "an error" but an excepted condition


so, if you are using raise_application_error, it must be a brand new error that happened during the normal processing of an expected exception.


Meaning, catching when others to turn that into a generic "raise application error" -- why? why would you do that? It only serves to hide the root cause, to lose information. it falls into the "when others is evil" category.

if your CAUGHT the exception, it was EXPECTED -- is not an ERROR. So, why tell someone about a thing that is not an error?


and if it were an error, the proper thing would be "raise;", not raise_application_error.


Do you in java code catch an exception simply to turn it into some other exception?