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.)
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.
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
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.