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?
Best Answer
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:
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:
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.