1)
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.
For example, lets say you have a procedure that will either INSERT a new record or UPDATE
an existing one depending on whether or not it exists. You could code:
begin
insert into t ( columns.... ) values ( values ..... );
exception
when dup_val_on_index then -- record already exists, lets update it
update t set .... = .... where ....;
end;
Now, if that was coded:
begin
insert into t ( columns.... ) values ( values ..... );
exception
when dup_val_on_index then -- record already exists, lets update it
update t set .... = .... where ....;
when others then
null;
end;
that would be a bug. The when others would fire upon some spurious -- un-expected error
and the record would be neither added nor updated. It would be skipped.
Same with your routines, if you have a when others -- and don't do anything meaningful in
it (eg: email yourself a notification that it failed, log a message using utl_file or an
autonomous transaction, etc -- it is an error that will go undetected.
I truly wish we didn't even support WHEN OTHERS.
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)
Partager