sql - Does Oracle roll back the transaction on an error?

Question

This feels like a dumb question, but I see the following in the Oracle concepts guide on transaction management:

A transaction ends when any of the following occurs:

A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.

A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If the current transaction contains any DML statements, Oracle first commits the transaction, and then runs and commits the DDL statement as a new, single statement transaction.

A user disconnects from Oracle. The current transaction is committed.

A user process terminates abnormally. The current transaction is rolled back.

Am I to interpret the last point to mean that if I issue a query that has an error, the transaction will get rolled back?

Answer 1

this is an interesting question !

When Oracle encounters an error, it will rollback the current statement, not the transaction. A statement is any top-level instruction, it can be a SQL statement (INSERT, UPDATE...) or a PL/SQL block.

This means that when a statement (for example a pl/sql procedure called from java) returns an error, Oracle will put the transaction in the same logical state as before the call. This is immensely helpful, you don't have to worry about half-executed procedures (**).

This thread on AskTom covers the same topic:

[the statement] either ENTIRELY happens or it ENTIRELY DOES NOT happen and the way that works is the database does the logical equivalent of:

begin
   savepoint foo;
   <<your statement>>
exception
   when others then rollback to foo; 
                    RAISE;
end;

This feature, in my opinion, is why it is a lot easier to write database code (*) in pl/sql than in any other language.

(*) code that interacts with an Oracle DB of course, I suppose the native procedural languages of the other DBMS have similar features.

(**) This only concerns DML since DDL are not transactional in Oracle. Be also careful with some DBMS packages that update the data dictionary (such as DBMS_STATS), they often do DDL-like changes and issue commits. Refer to the documentation in case of doubts.

Update: this behaviour is one of the most important concept in PL/SQL, I will provide a small example to demonstrate the atomicity of the pl/sql statements:

SQL> CREATE TABLE T (a NUMBER);

Table created

SQL> CREATE OR REPLACE PROCEDURE p1 AS
  2  BEGIN
  3     -- this statement is successful
  4     INSERT INTO t VALUES (2);
  5     -- this statement will raise an error
  6     raise_application_error(-20001, 'foo');
  7  END p1;
  8  /

Procedure created

SQL> INSERT INTO t VALUES (1);

1 row inserted

SQL> EXEC p1;

begin p1; end;

ORA-20001: foo
ORA-06512: at "VNZ.P1", line 5
ORA-06512: at line 2

SQL> SELECT * FROM t;

         A
----------
         1

Oracle has rolled back the transaction to the point just before calling p1. There is no half-work done. It is as if the procedure p1 had never been called.