Why WHEN OTHERS THEN RAISE Can Be Harmful

Tweet

Every PL/SQL developer knows, or at least should know, that WHEN OTHERS THEN NULL is a hidden bug; Ask Tom.

But, what about WHEN OTHERS THEN RAISE?

I have known PL/SQL developers who follow a “standard” of adding:

EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END;

to every PL/SQL block. The reasoning behind this practice, as explained to me:

• There is no harm in adding it
• It makes maintenance easier
• Used as a placeholder for additional error handling when needed
• There is no overhead executing it

When asked if he was joking when he added “when others then raise;” Shoblock replied:

not joking at all. very serious. I know it’s not needed, but it helps to point out to people who might modify your code later on that you do indeed want all other exceptions to be raised. it basically prevents someone from adding “when others then null” while you’re on vacation.

Now, before rushing back and starting to sprinkle WHEN OTHERS THEN RAISE all over your code, please read Tom Kyte’s take on this seemingly harmless line of code:

why do people do this?????

EXCEPTION WHEN OTHERS THEN RAISE; END;

what is the point, other than to make the code infinitely harder to debug. You know what you did by coding that?

YOU HIDE THE LINE NUMBERS FROM US. No longer can you tell where the error came from !!!!!

Tom is right. Consider:

SQL> DECLARE
  2     l_var   varchar2 (30);
  3  BEGIN
  4     SELECT   object_name
  5       INTO   l_var
  6       FROM   all_objects
  7      WHERE   ROWNUM <= 2;
  8  END;
  9  /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4

Now let’s add WHEN OTHERS THEN RAISE:

SQL> DECLARE
  2     l_var   varchar2 (30);
  3  BEGIN
  4     SELECT   object_name
  5       INTO   l_var
  6       FROM   all_objects
  7      WHERE   ROWNUM <= 2;
  8  EXCEPTION
  9     WHEN OTHERS
 10     THEN
 11        RAISE;
 12  END;
 13  /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 11

As you can see, WHEN OTHERS THEN RAISE masked the line number where the actual error occurred. In the above example, the real error occurred at line 4 not 11.

WHEN OTHERS THEN NULL is a hidden bug but WHEN OTHERS THEN RAISE hides the bug.

Related articles:

• How to find where an error was raised in PL/SQL
• CASE gotcha in Oracle 8i
• NO_DATA_FOUND Gotcha

Filed in Oracle, Tips on 14 Apr 09 | Tags: exception

---