WHEN OTHERS THEN NULL

March 28, 2010

in exceptions, plsql

A few quick questions for you. Write your answers in the comments. Solutions will be published on 2nd April 2010. [Update: Answers published!]

[In an Oracle interview, your answers to these questions can be deal-breakers. Think carefully!]

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.

1. What is the purpose of adding the exception handler to the code? What would happen if the exception handler were not there?

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 the primary key – 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

2. What are the advantages and disadvantages of adding the WHEN OTHERS THEN NULL exception handler?

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 article on OTN, 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.

{ 2 comments… read them below or add one }

prakhar March 31, 2010 at 9:06 pm

1)What is the purpose of adding the exception handler to the code? What would happen if the exception handler was not there?
If error occurs while inserting ther rec(say), Your procedure will silently return to the calling block without any errors (post processing the Excep section)
If you dont specify the excep handler, then the insertion exception (say)will be propagated to the calling block, & in this block there should be any excep. handller

2)What are the advantages and disadvantages of adding the WHEN OTHERS THEN NULL exception handler?
————-

oratabler April 1, 2010 at 11:12 am

Hi Prakhar, Welcome here, and you’re right! The question, then, is: Is this effect of WHEN OTHERS THEN NULL a good thing or a bad thing?

Leave a Comment

Previous post:

Next post: