PLSQL "All or Nothing" Pitfall

Transactions are such a common thing when working with databases. They act on an "all or nothing" basis, that is, they succeed or fail but they always should let the database into a consistent state. Of course, in Oracle databases the rules are the same, but the interesting part I want to refer to is in connection with PL/SQL modules (procedures, functions or packages).

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.