Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: question about plsql exceptions
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;
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;
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