WHEN OTHERS THEN NULL – A bug

A when others is almost always a BUG unless it is immediately followed by a RAISE.                    

– Quote from Thomas Kyte’s excellent thread on similar topic

Remember, for errors, RAISE –> CATCH –> HANDLE

The most common mistake that pl/sql developers often do is not handling the exceptions properly. Irrespective of the fact that the code is robust, having a weak exception handler could break the entire code and make the entire business logic prone to errors. We all know how painful it is to debug a huge piece of code just to find the line where the error occurs. Yes, it is time consuming and could be avoided most of the times if good coding practices are followed.

At first instance, why do we need an exception handler? To catch the errors, log them(optional), and finally do something about them. Isn’t it? If not, then why do we need the overhead of catching them and ultimately not doing anything?

In this post, I am restricting the scope to the ultimate bug in any pl/sql code, none other than “WHEN OTHERS THEN NULL”. Yes, it’s a bug waiting for its chance to break the atomicity of a procedural call.

Let’s see why.

A simple test case to show how WHEN OTHERS hides the actual error if not re-raised:

SQL> set serveroutput on;

SQL> CREATE OR REPLACE PROCEDURE p_test_others(i_val IN VARCHAR2) AS
2           o_val NUMBER;
3        BEGIN
4           SELECT i_val INTO o_val FROM dual;
5           DBMS_OUTPUT.PUT_LINE(o_val);
6        EXCEPTION
7           WHEN OTHERS THEN
8              NULL;
9        END;
10      /
Procedure created

SQL> SHOW ERRORS;
No errors for PROCEDURE P_TEST_OTHERS

SQL> BEGIN
2          p_test_others('a');
3       END;
4       /
PL/SQL procedure successfully completed

So the above code did not throw any error and it says the procedure successfully completed. But, is it?

SQL> CREATE OR REPLACE PROCEDURE p_test_others(i_val IN VARCHAR2) AS
2       o_val NUMBER;
3    BEGIN
4       SELECT i_val INTO o_val FROM dual;
5       DBMS_OUTPUT.PUT_LINE(o_val);
6    EXCEPTION
7       WHEN OTHERS THEN
8          DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE);
9          DBMS_OUTPUT.PUT_LINE('Message: '||SQLERRM);
10   END;
11   /
Procedure created

SQL>
SQL> BEGIN
2       p_test_others('a');
3    END;
4    /
SQLCODE: -6502
Message: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
PL/SQL procedure successfully completed

So, we do see an error being captured. However, it is only as good as knowing that the error exists, but what needs to be done with it? If such a code is a part of any job, the job would simply complete successfully. Do you to proceed blindly with errors?

You need to re-raise the error.

SQL> CREATE OR REPLACE PROCEDURE p_test_others(i_val IN VARCHAR2) AS
2       o_val NUMBER;
3    BEGIN
4       SELECT i_val INTO o_val FROM dual;
5       DBMS_OUTPUT.PUT_LINE(o_val);
6    EXCEPTION
7       WHEN OTHERS THEN
8          DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE);
9          DBMS_OUTPUT.PUT_LINE('Message: '||SQLERRM);
10         RAISE;
11    END;
12    /
Procedure created
SQL>
SQL> BEGIN
2          p_test_others('a');
3       END;
4       /
SQLCODE: -6502
Message: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
BEGIN
p_test_others('a');
END;
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "P_TEST_OTHERS", line 10
ORA-06512: at line 2

So where to use WHEN OTHERS? Confined to the following cases :

• Error logging.
     when others
        then
        log_the_error(required_parameters);
        raise;
     end;
• To catch all other exceptions apart from the Named System Exceptions and Named Programmer-Defined Exceptions.
• Send an email regarding the error captured(Might be helpful during development stage or unit testing).
• To close all the opened resources such as Cursors, files etc.