Classic example of why I despise...
WHEN OTHERS. When not followed by RAISE; to re-raise the error.
What is really bothersome:
I am having this problem since a months (sic)
Ok, they've been stuck in their upgrade (probably cursing 10g in the meanwhile), all because of this stupid "when others" clause. I despise it, I want it gone. I know - there are valid uses of it, for example:
log_error( ...... ); -- log error is an autonomous transaction
but you know what, I just don't see it used correctly in most cases, yes MOST cases. People feel compelled for whatever reason to hide errors from client applications - and always include that fateful when others without the raise;. I cannot count the number of bugs in existing systems that I've found - let alone the ones that are out there just waiting to spring out and bite someone.
I am just thankful they wrote a function, and forgot to return anything in the when others. Small things like making two fatal errors sometimes cancel each other out and make for something that works!!!
If I could remove three things from the database - they would be:
- Autonomous Transactions
- When Others (at least if not followed by RAISE or make when others ALWAYS re-raise as part of the language itself!!)
Triggers are so abused - and used so inappropriately, I'd rather live without them.
Autonomous transactions (coupled with triggers of course, to avoid mutant tables or something) are another "bug in the disguise of production code". Almost universally misapplied.
When Others - 'nuff said.
I don't even know what to say about doing DDL in the function like they do - or the lack of bind variables. My afternoon is wrecked after seeing this piece of "code". Utterly destroyed.