msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Why WHEN OTHERS THEN RAISE Can Be Harmful

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.

Filed in Oracle, Tips with 4 Comments | Tags:


Here’s a Quick Way to Get the Line Number in PL/SQL

In Oracle 10g, the PLSQL_LINE predefined inquiry directive is a PLS_INTEGER literal value indicating the line number reference to $$PLSQL_LINE in the current program unit. In other words, $$PLSQL_LINE is the number of the line where $$PLSQL_LINE appears in your PL/SQL code.

Another useful predefined inquiry directive is PLSQL_UNIT which is a VARCHAR2 literal value indicating the current source program unit. For a named compilation unit, $$PLSQL_UNIT contains the unit name. For an anonymous block, $$PLSQL_UNIT is NULL.

Now, on to some examples: (more…)

Filed in Oracle, Tips with 2 Comments | Tags: ,


Little known way to get the error message in PL/SQL

There are two built-in Oracle PL/SQL functions that return the error message associated with the error code of the most recently raised exception in your PL/SQL block. The first function is SQLERRM. The second is the less known function DBMS_UTILITY.FORMAT_ERROR_STACK.

Which one to use? To answer this question, let us first review the details of each function. (more…)

Filed in Oracle with 5 Comments | Tags: ,


How to find where an error was raised in PL/SQL

To eliminate errors in your Oracle PL/SQL programs, it is very important to find the line number on which the error had occurred. The question is how to find that line number.

Before Oracle Database 10g Release 1, the only way to know the line number is to let the exception go unhandled in your PL/SQL code.

In Oracle Database 10g Release 1 and above, you can take advantage of the new function DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. This new function returns a formatted string that displays a stack of programs and line numbers leading back to the line on which the error was originally raised.

(more…)

Filed in Oracle with 4 Comments | Tags: ,