RE: Interesting Exploit in PL/SQL

I have no problem with the "when others then" clause. It's the NULL thereafter that irks me & yes I know Oracle does things like that in their own code. Doesn't make it right though. What I prefer, and yes I do use this in my own code, is :

   exception

        when others then msg := msg ||':'|| sqlerrm;  

<client>_mail('HelpDesk<helpdesk@[client].com>','Error in Plsql Procedure <name>',msg);

   end;

Where <client>_mail is a plsql wrapper around smtp_mail that sends the error message & a pointer into the procedure where the error occurred. At least this way the code runs, but the error is trapped for later diagnosis & repair.  

Dick Goulet, Senior Oracle DBA
45 Bartlett St Marlborough, Ma 01752, USA Tel.: 508.573.1978 |Fax: 508.229.2019 | Cell:508.742.5795 RGoulet_at_kanbay.com
: POWERING TRANSFORMATION -----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nuno Souto Sent: Wednesday, November 29, 2006 3:26 AM Cc: Oracle-L Freelists
Subject: Re: Interesting Exploit in PL/SQL

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?

--
Cheers
Nuno Souto
in windy Sydney, Australia
dbvision_at_iinet.net.au
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l