It is interesting to note that Oracle considers PL/SQL blocks to be statements as well.
Consider the following stored procedure and reset of the example tables:
$ sqlplus eoda/foo@PDB1
SQL> create or replace procedure pasbegin insert into t values ( 1 );insert into t values (-1 );end;/
So, we have a procedure we know will fail, and the second INSERT will always fail in this case. Let’s see what happens if we run that stored procedure:
SQL> beginp;end;/
As you can see, Oracle treated the stored procedure call as an atomic statement. The client submitted a block of code—BEGIN P; END;—and Oracle wrapped a SAVEPOINT around it. Since P failed, Oracle restored the database back to the point right before it was called.
Note The preceding behavior—statement-level atomicity—relies on the PL/ SQL routine not performing any commits or rollbacks. It is my opinion that COMMIT and ROLLBACK should not be used in general in PL/SQL; the invoker of the PL/SQL stored procedure is the only one that knows when a transaction is complete. It is a bad programming practice to issue a COMMIT or ROLLBACK in your developed PL/ SQL routines.
Here, we ran a block of code that ignored any and all errors, and the difference in outcome is huge. Whereas the first call to P effected no changes, this time the first INSERT succeeds, and the CNT column in T2 is incremented accordingly.
Oracle considered the “statement” to be the block that the client submitted. This statement succeeded by catching and ignoring the error itself, so the If error then rollback… didn’t come into effect, and Oracle didn’t roll back to the SAVEPOINT after execution.
Hence, the partial work performed by P was preserved. The reason this partial work is preserved in the first place is that we have statement-level atomicity within P: each statement in P is atomic. P becomes the client of Oracle when it submits its two INSERT statements. Each INSERT either succeeds or fails entirely.
This is evidenced by the fact that we can see that the trigger on T fired twice and updated T2 twice, yet the count in T2 reflects only one UPDATE. The second INSERT executed in P had an implicit SAVEPOINT wrapped around it.
THE “WHEN OTHERS” CLAUSE
I consider virtually all code that contains a WHEN OTHERS exception handler that does not also include a RAISE or RAISE_APPLICATION_ERROR to re-raise the exception to be a bug. It silently ignores the error, and it changes the transaction semantics. Catching WHEN OTHERS and translating the exception into an old-fashioned return code changes the way the database is supposed to behave.
In fact, when Oracle 11g Release 1 was still on the drawing board, I was permitted to submit three requests for new features in PL/SQL. I jumped at the chance, and my first suggestion was simply “remove the WHEN OTHERS clause from the language.” My reasoning was simple: the most common cause of developer-introduced bugs I see—the most common cause—is a WHEN OTHERS not followed by a RAISE or RAISE_APPLICATION_ERROR. I felt the world would be a safer place without this language feature. The PL/SQL implementation team could not do this, of course, but they did the next best thing.
They made it so that PL/SQL will generate a compiler warning if you have a WHEN OTHERS that is not followed by a RAISE or RAISE_APPLICATION_ERROR call. For example:
SQL> alter session setPLSQL_Warnings = ‘enable:all’/Session altered.
SQL> create or replace procedure some_proc( p_str in varchar2 )
So, if you include WHEN OTHERS in your code and it is not followed by a RAISE or RAISE_APPLICATION_ERROR, be aware that you are almost certainly looking at a bug in your developed code, a bug placed there by you.
The difference between the two blocks of code, one with a WHEN OTHERS exception block and one without, is subtle and something you must consider in your applications. Adding an exception handler to a block of PL/SQL code can radically change its behavior.
A different way to code this—one that restores the statement-level atomicity to the entire PL/SQL block—is as follows:
SQL> beginsavepoint sp;p;exception when others thenrollback to sp;dbms_output.put_line( ‘Error!!!! ‘ || sqlerrm );end;
Caution The preceding code represents an exceedingly bad practice. In general, you should neither catch a WHEN OTHERS nor explicitly code what Oracle already provides as far as transaction semantics is concerned.
Here, by mimicking the work Oracle normally does for us with the SAVEPOINT, we are able to restore the original behavior while still catching and “ignoring” the error.
I provide this example for illustration only; this is an exceedingly bad coding practice.
