A PL/SQL module is some kind of "all or nothing" component. If the procedure fails it rollbacks the uncommited work it has done. Suppose we have the following procedure:
CREATE OR REPLACE PROCEDURE test AS
BEGIN
insert into yyy values (1);
raise_application_error(-20000, 'I am a cute error!');
END test;
Let's see what happens:
SQL> truncate table yyy;
Table truncated.
SQL> exec test;
BEGIN test; END;
*
ERROR at line 1:
ORA-20000: I am a cute error!
ORA-06512: at "TALEK.TEST", line 4
ORA-06512: at line 1
SQL> select * from yyy;
no rows selected
Nice... we didn't explicitly rollback, but Oracle was smart enough to do the cleanup job for us. This makes sense and proves that PLSQL modules are, in a way, "all or nothing" components.
Now, let's say we have an oracle job which calls our "test" procedure and if an error occurs it has to log it into another table. A possible implementation of the job PLSQL caller block may be:
begin
test;
exception
when others then
insert into log values (dbms_utility.format_error_stack);
commit;
raise;
end;
/
The above code may seem harmless: the test procedure is called and if it raises an error the exception part of the PL/SQL caller block is executed which further inserts the error into our log table. Of course, we commit the log entry we just inserted and we re-raise the originating error. We know that if test procedure fails then it rollbacks its uncommited work as we seen above. After all, it's an "all or nothing" piece, right? Well, here's the pitfall: if you catch the exception then the procedure which raised the error will not clean up anything as long as you are within the EXCEPTION section. Even the whole anonymous block will fail because of re-raising the original error, the COMMIT statement from the EXCEPTION section will actually commit the incomplete work done by our "TEST" procedure. So, in most cases you should look twice to such EXCEPTION WHEN THEN ... COMMIT definitions... otherwise you may end up with nasty bugs. In the above example, in order to avoid this problem, a ROLLBACK should be performed before logging the error. Of course, there are smarter logging solutions which use autonomous transactions but, anyway, the goal was just to reveal the pitfall.
Posts
