WHEN OTHERS THEN NULL

Look at this small piece of code which inserts a row into the EMP table.

You see an exception block in there. The questions are:

1. What is the purpose of adding the exception handler to the code? What would happen if the exception handler were not there?
2. What are the advantages and disadvantages of adding the WHEN OTHERS THEN NULL exception handler?

create or replace procedure pr_insert_emp is
begin
 insert into emp (
 empno
 , ename
 )
 values                 (
 10
 , 'test'
 );
exception
 when others then
 null;
end;
/

Bring on those comments.

Update (2nd April 2010): As promised, the answers to the two questions posted above.

As Prakhar at comment#1 points out, WHEN OTHERS THEN NULL suppresses any error that might occur in the procedure.

If the exception handler were not there, then in case something goes wrong with the code – say, a duplicate record is inserted which violates a unique constraint – an error will be raised.

To see this in action, check out the runtime results for the procedure, with and without the exception handler.

SQL> -- With exception handler WHEN OTHERS THEN NULL, no error raised
SQL> create or replace procedure pr_insert_emp is
 2   begin
 3       insert into emp (
 4                                             empno
 5                                         , ename
 6                                       )
 7          values                 (
 8                                             10
 9                                         ,  'test'
10                                       );
11   exception
12       when others then
13           null;
14   end;
15   /

Procedure created.

SQL> exec pr_insert_emp;

PL/SQL procedure successfully completed.

SQL> -- Without exception handler WHEN OTHERS THEN NULL, PK violation  error is raised
SQL> create or replace procedure pr_insert_emp is
 2   begin
 3       insert into emp (
 4                                             empno
 5                                         , ename
 6                                       )
 7          values                 (
 8                                             10
 9                                         ,  'test'
10                                       );
11   end;
12   /

Procedure created.

SQL> exec pr_insert_emp;
BEGIN pr_insert_emp; END;

*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_EMP)  violated
ORA-06512: at "SCOTT.PR_MODIFY", line 3
ORA-06512: at line 1

WHEN OTHERS THEN NULL can be a very dangerous construct to put into your code. It is equivalent to ignoring the error and pretending as if nothing happened – but if an error didn’t matter enough to be reported and noticed, then there is clearly something wrong with the logic behind the code.

Oracle technologist Tom Kyte strongly opposes the use of WHEN OTHERS THEN NULL. He goes so far as call it a bug in the code. In this blog post, Tom Kyte says:

If you have code wrapped with an exception block that contains WHEN OTHERS-but does not reraise the exception-all of the code in that exception block can be safely deleted, in my opinion. You do not need it, because if the code fails to execute, you ignore the fact that it did not execute.

In the above procedure pr_insert_emp, the code would apparently be successful whether or not it inserted the record. If it does not matter either way, we need to ask: why does the procedure exist at all?

If you have written code in which WHEN OTHERS THEN NULL exists, please review it carefully. Chances are you don’t need it, and it is or could do a world of damage. Unless you have a very good reason for having it (and I’ll be most interested to know the reason, please share), remove it right away!

Note: In Oracle 11G, a new warning message (PLW-06009) has been introduced. Code containing WHEN OTHERS THEN NULL without RAISE gives a warning at compile time.