you are showing some signs of "paranoia" here - break down of the volume of consulting services. hmmm..
Anyway - come on, this is programming 101 stuff here people. This is NOT an Oracle thing. Exceptions happen in most current languages today.
This is a programming thing, something that should be taught to programmers in school, in classes, in the programming books they read, by their mentors.
This is not an Oracle thing
This is a programming thing, period.
And the exception handling I see in Java, C++, PL/SQL - it quite simply
scares
me
to
death
I don't care WHOSE code it is in - I'll flame on regardless. I've had these discussions with programmers all over the place. They are doing it wrong.
IF (you have a when others Not followed by RAISE or RAISE_APPLICATION_ERROR (if you do not re-raise the error)
THEN
you have a bug
END IF;
I don't care who you are, how long you've been writing code.
I've written about this until I'm blue in the face.
Flame on every time I see it now, Just scares me to death.
Yes, when I find it in developed Oracle code I raise a fuss, I've made more than one person "annoyed" at me - but the only way I seem to be able to get the point across is "hey - you have a bug, yes you do, nothing you say will change that fact, face it, you have a bug - a bug - a big old bug"
even if you have code like this:
begin
execute immediate 'drop table t;';
exception
when others the null; -- we can ignore this because if the table
-- isn't there we don't care
end;
a lot of people would say "that is safe"
I would say "you are lazy, and it is not safe, I can make your drop table fail for hundreds of reasons - none of them to do with "table or view does not exist"
That code should
a) verify table T is a table (query dictionary)
b) drop it
c) upon hitting an error - feel free to log is BUT YOU BETTER RE-RAISE IT
why a) - because we are expecting to drop a table, if T is a view - it is NOT what we were expecting, un-expected stuff means "STOP". If T is anything other than a table - STOP.
why c) because you might want to log the error, for posterity - but you cannot deal with it (exception blocks that do not re-raise an exception mean YOU DEALT WITH THE ERROR, THE ERROR NEVER HAPPENED). You have to re-raise it.
And everyone up the call tree must do the same - if the catch it, it is unlikely they can "fix it", so they best either a) ignore it (do not catch it), b) catch it, do whatever, RE-RAISE IT (or raise SOMETHING)
http://www.google.com/search?q=site%3Atkyte.blogspot.com+when+others+then+null One of my favorite 11g new features:
ops$tkyte%ORA11GR1> create table t( x varchar2(4000) );
Table created.
ops$tkyte%ORA11GR1> create or replace
2 procedure maintain_t
3 ( p_str in varchar2 )
4 as
5 begin
6 insert into t
7 ( x ) values
8 ( p_str );
9 exception
10 when others
11 then
12 -- call some log_error() routine
13 null;
14 end;
15 /
Procedure created.
ops$tkyte%ORA11GR1> exec maintain_t( rpad( 'x', 4001, 'x' ) );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR1> select * from t;
no rows selected
ops$tkyte%ORA11GR1> alter procedure maintain_t compile
2 PLSQL_Warnings = 'enable:all'
3 reuse settings
4 /
SP2-0805: Procedure altered with compilation warnings
ops$tkyte%ORA11GR1> show errors procedure maintain_t
Errors for PROCEDURE MAINTAIN_T:
LINE/COL ERROR
-------- -----------------------------------------------------------------
9/8 PLW-06009: procedure "MAINTAIN_T" OTHERS handler does not end in
RAISE or RAISE_APPLICATION_ERROR
I can now use the compiler to more quickly correct code. Now when a developer says to me "sometimes Oracle doesn't save my changes" - I will recompile their code with warnings set high and find all of these and tell them to add raise to it.
it will become immediately obvious where their bug is.