Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Interesting Exploit in PL/SQL

Re: Interesting Exploit in PL/SQL

From: Nuno Souto <>
Date: Wed, 29 Nov 2006 19:26:08 +1100
Message-ID: <>

Tim Gorman wrote,on my timestamp of 29/11/2006 1:35 AM:
> Please read it more carefully. "WHEN OTHERS THEN NULL" is *never*
> acceptable; he's saying to use WHEN OTHERS to close any cursors opened
> by DBMS_SQL before leaving the program unit.
> In fact, WHEN OTHERS THEN NULL will still leave cursors open and
> exploitable, while neatly concealing the nature of any encountered
> exception, in addition to concealing the fact that any error occurred at
> all, to boot.
> It is a piece of code that should be flagged as a compilation error by
> the PL/SQL compiler.
> For coding stupidity, "WHEN OTHERS THEN NULL" is closely rivaled by
> "WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('A serious error has occurred')",
> which brought a large consumer retail e-commerce website to its knees
> during Christmas season a few years ago. Talk about a million-dollar
> piece of code!

Well yes, that's all very nice and according to the latest "rules". However, let's never forget:

1- I wish I had $1 for every block of PL/SQL code out there who has WHEN OTHERS THEN NULL coded explicitly or implicitly. It ain't gonna change overnight! Or does anyone believe it will?

2- Oracle's OWN code has it. I know, that is not a recommendation. But it's a fact. And nothing will make it change into oblivion.

3- It IS a perfectly valid syntax and is NOT flagged as an error.

4- If you don't know what error can be generated but you MUST run the code nevertheless, then you MUST use WHEN OTHERS THEN NULL. Or pay the price of a temp table to keep all possible return codes. Otherwise your code won't run. No, I will NOT recommend coding exceptions for 20000 possible error returns!

So, rather than embark in yet another crusade of calling anyone who doesn't follow the fad an incompetent by nature, can we just for once address the problem at the root?

And ask Oracle to fix it first rather than blame everyone else for it?

Nuno Souto
in windy Sydney, Australia
Received on Wed Nov 29 2006 - 02:26:08 CST

Original text of this message