Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: question about plsql exceptions

Re: question about plsql exceptions

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Thu, 23 Feb 2006 17:56:56 +0100
Message-ID: <dtkpgq$mrs$1@news.BelWue.DE>


stefanomnn_at_yahoo.it wrote:
> uau.... it work well!
>
> Thanks!
>

However, it's bad practice.
When others should be followed by a raise. If it's not, it's a bug waiting to happen.

Consider the following (which is shamelessly copied from the latest book by Tom Kyte, very commendable read):

SQL> create table t (x int);

Table created.

SQL> create unique index idx_t on t(x);

Index created.

SQL> create or replace procedure p

   2 is
   3 begin
   4 insert into t values (1);
   5 end;
   6 /

Procedure created.

SQL> begin

   2    p;
   3    p;
   4  end;

   5 /
begin
*
ERROR at line 1:
ORA-00001: unique constraint (BAER.IDX_T) violated
ORA-06512: at "BAER.P", line 4
ORA-06512: at line 3


SQL> select * from t;

no rows selected

Ok, nothing really new here. We know that a pl/sql block is considered an atomic operation that either succeeds completely or fails completely. Now let's introduce an exception handler:

SQL> begin

   2 begin

   3      p;
   4      p;
   5    exception
   6      when others then null;

   7 end;
   8 end;
   9 /

PL/SQL procedure successfully completed.

SQL> select * from t;

          X


          1

SQL> Ooopsy, a pl/sql block that partly succeeded? If you code accordingly, that might even be the expected outcome. Or if you don't mind that you can't predict the outcome of a given procedure, that's fine too. But most likely, there will be an exception that will bite you in the proverbial when you least expect it. And since it's silently ignored due to the when others clause, your users won't even notice that something's gone wrong.

Catch exceptions that you expect, like the dup_val_on_index in the above example, but raise all other exceptions. After all, they are exceptional!

Regards,
Holger Received on Thu Feb 23 2006 - 10:56:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US