WHEN OTHERS
In forum part of this site, we often get PL/SQL topics from people asking why their code sometimes works and sometimes does not, sometimes makes all the expected modifications and sometimes only some or none of them.
Some other questions are about how to debug a code, where to start, when the code just gives an error message.
When the code is posted, we can immediatly see the reason: code contains some EXCEPTION WHEN OTHERS clause. This Wiki page will show through examples why this clause is misleading and dangerous.
WHEN OTHERS hides the error
See the following execution of code with a procedure which just inserts a row in a table T:
SQL> SELECT * FROM t; no rows selected SQL> CREATE OR REPLACE PROCEDURE p 2 IS 3 v INTEGER; 4 BEGIN 5 v := 1; 6 FOR i IN 1..3 LOOP 7 v := 10 * v; 8 END LOOP; 9 INSERT INTO t VALUES (v); 10 EXCEPTION WHEN OTHERS THEN dbms_output.put_line (SQLERRM); 11 END; 12 / Procedure created. SQL> EXECUTE p; PL/SQL procedure successfully completed.
Everything seems to have executed well and we can check it querying the table:
SQL> SELECT * FROM t; no rows selected
Gosh! Nothing in my table but I did insert and Oracle said it did it: there was no error!
Let's comment the WHEN OTHERS clause:
SQL> CREATE OR REPLACE PROCEDURE p 2 IS 3 v INTEGER; 4 BEGIN 5 v := 1; 6 FOR i IN 1..3 LOOP 7 v := 10 * v; 8 END LOOP; 9 INSERT INTO t VALUES (v); 10 -- EXCEPTION WHEN OTHERS THEN dbms_output.put_line (SQLERRM); 11 END; 12 / Procedure created. SQL> EXECUTE p; BEGIN p; END; * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column ORA-06512: at "MICHEL.P", line 9 ORA-06512: at line 1
Oh oh! There was a value error hidden by WHEN OTHERS. Let's see the table definition:
SQL> DESCRIBE t Name Null? Type -------------------------------- -------- ---------------------- VAL NUMBER(3)
Indeed the column can't get a value greater than 999 and I tried to insert 1000. One could say "you didn't see the message because you didn't set serveroutput on", but why should I? And even, let's see in the next section what happens with a more complex code.
WHEN OTHERS hides where the error comes from
See this other example (code does not matter):
SQL> SET SERVEROUTPUT ON SQL> CREATE OR REPLACE PROCEDURE p 2 IS ... 203 EXCEPTION WHEN OTHERS THEN dbms_output.put_line (SQLERRM); 204 END; 205 / Procedure created. SQL> EXECUTE p ORA-01476: divisor is equal to zero PL/SQL procedure successfully completed.
I have an error in my code but where should I start to debug it?
Now just comment WHEN OTHERS clause:
SQL> CREATE OR REPLACE PROCEDURE p 2 IS ... 203 -- EXCEPTION WHEN OTHERS THEN dbms_output.put_line (SQLERRM); 204 END; 205 / Procedure created. SQL> EXECUTE p BEGIN p; END; * ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at "MICHEL.P", line 69 ORA-06512: at "MICHEL.P", line 105 ORA-06512: at "MICHEL.P", line 154 ORA-06512: at line 1
I now know that the error comes from line 69 which is in a procedure that was called at line 105 which itself was called in main block at line 154. I then know where the error came from and so I am able to start debugging my code.
Note: Even if you add a "RAISE;" statement in your exception block it is not correct as you will still hide the actual origin of the error. See the following simple case:
SQL> declare a pls_integer := 1; b pls_integer := 0; 2 begin 3 b := a/b; 4 exception when others then raise; 5 end; 6 / declare a pls_integer := 1; b pls_integer := 0; * ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at line 4
Does the error really come from line 4? But there is nothing at line 4 that can raise a "divisor is equal to zero" error! Now if you comment the exception block:
SQL> declare a pls_integer := 1; b pls_integer := 0; 2 begin 3 b := a/b; 4 -- exception when others then raise; 5 end; 6 / declare a pls_integer := 1; b pls_integer := 0; * ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at line 3
You have then the actual line that raises the error: line 3.
WHEN OTHERS breaks the Atomicity of a procedure call
The first ACID property of a transaction is Atomicity. This means that either a transaction succeeds and all actions done are commited, either it fails at one point and all that has been done is rolled back which means it is like nothing have been done.
Oracle extends this transactional property to each SQL statement and even each PL/SQL anonymous block or stored procedure call.
See the following example:
SQL> create or replace procedure p is
2 begin
3 insert into t values (1);
4 dbms_output.put_line ('First row inserted');
5 insert into t values (1);
6 dbms_output.put_line ('Second row inserted');
7 end;
8 /
Procedure created.
SQL> select * from t;
no rows selected
SQL> execute p;
First row inserted
BEGIN p; END;
*
ERROR at line 1:
ORA-00001: unique constraint (MICHEL.SYS_C0012769) violated
ORA-06512: at "MICHEL.P", line 5
ORA-06512: at line 1
SQL> select * from t;
no rows selected
As you can see the first row was inserted but the procedute fails to insert the second one so Oracle rolled back the first insert statement. The procedure call was treated as a single statement and as it fails nothing have been done.
Now let's see what happens with WHEN OTHERS clause:
SQL> create or replace procedure p is
2 begin
3 insert into t values (1);
4 dbms_output.put_line ('First row inserted');
5 insert into t values (1);
6 dbms_output.put_line ('Second row inserted');
7 exception
8 when others then -- (whatever but raise or raise_application_error)
9 dbms_output.put_line ('Failed to insert a row');
10 end;
11 /
Procedure created.
SQL> select * from t;
no rows selected
SQL> execute p;
First row inserted
Failed to insert a row
PL/SQL procedure successfully completed.
SQL> select * from t;
ID
----------
1
1 row selected.
Then we see one row has been created, only one, which means the atomicity of the procedure call is broken. Something has been done but you (as the caller program) don't know how much of the procedure has been done and how much has not be done.
When to use WHEN OTHERS?
Actually, the only cases you have to use WHEN OTHERS are the following ones:
- You opened some resources (cursor, file...) and have to close them before leaving
- You want to log all errors
In both cases, the WHEN OTHERS THEN part MUST be ended with a "RAISE;" statement.
