[UPDATE 2014/11/17: Horrible copy/paste error! My PL/SQL code below left out the all-important
RAISE; command in the exception handler. Many thanks to Dom Brooks for giving me a heads up.]
So important yet often ignored or misunderstood! No, not me, but “statement level atomicity”.
Whenever you call the Oracle database to change data, the result will be all or nothing: Oracle will either do everything you asked it to, or nothing at all. This is true for SQL and PL/SQL.
What the documentation says
From the Concepts guide:
If a SQL statement causes an error during execution, then it is not successful and so all effects of the statement are rolled back. This operation is a statement-level rollback. This operation has the following characteristics:
- A SQL statement that does not succeed causes the loss only of work it would have performed itself.The unsuccessful statement does not cause the loss of any work that preceded it in the current transaction…
- The effect of the rollback is as if the statement had never been run.
There is no specific mention of PL/SQL here. I asked the author, Tom Kyte, about PL/SQL “statements” on asktom.oracle.com, “unhandled exceptions”. He replied:
any “statement” is an atomic statement.
every “statement” is
plsql is just a statement, so is update, they are all just statements.
all statements in Oracle are atomic…
I kept searching the Oracle documentation and finally found this in the TimesTen PL/SQL Developer’s Guide:
TimesTen PL/SQL differs from Oracle Database PL/SQL in a scenario where an application executes PL/SQL in the middle of a transaction, and an unhandled exception occurs during execution of the PL/SQL. Oracle Database rolls back to the beginning of the anonymous block. TimesTen does not roll back.
So there it is in black and white: it doesn’t matter whether you call the SQL engine or the PL/SQL engine, your “statement” either makes all the changes you asked for or none at all.
What Steven Feuerstein says
This blog was inspired by Steven’s recent tweet:
#147 #plsql Failure of SQL statement in your PL/SQL program will NOT force rollback of previously successful SQL statements.
— Steven Feuerstein (@sfonplsql) November 14, 2014
This may seem to contradict the Oracle documentation, but in fact it doesn’t. Steven is talking about what happens inside the PL/SQL code, whereas the documentation refers to what happens after the PL/SQL code has finished.
What my tests show
Here is a simple test case that shows how Steven is right.
CREATE TABLE T (N NUMBER); set serveroutput on declare procedure show_cnt(p_label in varchar2) is l_cnt number; begin select count(*) into l_cnt from t; dbms_output.put_line(p_label || ', count(*) = ' || l_cnt); end show_cnt; begin show_cnt('At beginning'); insert into t values(1); show_cnt('After good insert'); insert into t values('a'); exception when invalid_number then show_cnt('After bad insert'); -- the following line reraises the exception RAISE; end; / Error report - ORA-01722: invalid number ORA-06512: at line 16 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number. At beginning, count(*) = 0 After good insert, count(*) = 1 After bad insert, count(*) = 1 select count(*) from t; COUNT(*) ---------- 0 select count(*) trans_cnt from v$transaction; TRANS_CNT ---------- 0
Notice how the first insert did NOT get rolled back within the PL/SQL block, but it DID get rolled back after the block ended!
Notice also that, since the PL/SQL block was the first statement in a transaction, there is no transaction anymore. The situation is exactly as if the statement never ran in the first place.
- When a “client” calls the Oracle database, it doesn’t matter whether it calls the SQL engine or the PL/SQL engine, the entire call is a “statement” that will always be atomic.
- When PL/SQL (inside the database) calls the SQL engine, the SQL statement is atomic. If the execution goes wrong, only that SQL statement is rolled back.
- PL/SQL execution “goes wrong” if and only if an exception is passed back to the calling program. Without the
RAISEin my exception handler, the exception would never go back to the caller and Oracle would not roll back the statement!
- If the PL/SQL code does a
ROLLBACKitself, statement-level atomicity will not work as intended.