Does `EXCEPTION WHEN OTHERS THEN RAISE` do something?

Being still a newbie in PL/SQL, I've been copying and pasting around the following trigger:

CREATE OR REPLACE TRIGGER FOO_TRG1
    BEFORE INSERT
    ON FOO
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
BEGIN
    IF :NEW.FOO_ID IS NULL THEN
        SELECT FOO_SEQ1.NEXTVAL INTO :NEW.FOO_ID FROM DUAL;
    END IF;

    EXCEPTION
        WHEN OTHERS THEN RAISE;
END FOO_TRG1;
/
ALTER TRIGGER FOO_TRG1 ENABLE;

I suspect that the included exception handling code does nothing at all and could simply get removed, since I'll get an error message anyway if something goes wrong. Am I right?

(I guess such code is the result of further editing prior code.)

Answers


yes, that exception does nothing but raise the same error out. also it serves to mask the real line number of the error. i'd remove that if I were you.

eg:

SQL> declare
  2    v number;
  3  begin
  4    select 1 into v from dual;
  5    select 'a' into v from dual;
  6  exception
  7    when others
  8    then
  9      raise;
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 9

vs:

SQL> declare
  2    v number;
  3  begin
  4    select 1 into v from dual;
  5    select 'a' into v from dual;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 5

the line number in the first one is pointing to the raise instead of the real line number. it can make tracking down errors harder.


It doesn't just do nothing at all, it is verging on criminal. It basically says "Just for fun, I'm going to pretend that the error occurred at this line rather than the real line."

Exception handling is the most misunderstood aspect of the entire language. The above is very common, and I think it stems from the basic misconception that an 'unhandled exception' is a bad thing (it almost begs the question - why didn't you handle it?). If only Oracle had used the terminology 'exception occured' or 'exception encountered', us poor folks who have to support this code would have far fewer 'WHEN OTHERS' exception handlers making our lives miserable.


It does nothing at all but mask error line, as @DazzaL points. But it serves as a reminder, telling you that the code above can raise an exception that should be treated but treatment remains undefined.

In other programming languages you can create a try/catch block automatically (e.g. in Eclipse/Java, Netbeans/PHP or Visual Studio/C#), and it is created with a catch (like the "when others") wich default behaviour is throw again the exception, while programmer decide what should be done when an exception is raised.


Need Your Help

How to properly use jsPDF library

javascript jquery html jspdf

I want to convert some of my divs into PDF and I've tried jsPDF library but with no success. It seems I can't understand what I need to import to make the library work. I've been through the exampl...

Could not get unknown property 'assembleRelease' for project

android gradle android-studio-2.2

After updating Android Studio to version 2.2 and the gradle plugin to 2.2.0, I get following error: