BEGIN - END block atomic transactions in PL/SQL

Question

This information should be easy to find, but I haven't had any luck.

When I have a BEGIN - END block in a PL/SQL, does it behave as an atomic transaction, that will try to commit on hitting the END block and if anything goes wrong rolls back the changes?

If not, how do I make sure that the code inside the BEGIN - END block behaves like an atomic transaction and how does the block behave "by default"?

EDIT: I am running from a stored procedure and I am using an implicit block, I think.

Answer 1

Firstly, BEGIN..END are merely syntactic elements, and have nothing to do with transactions.

Secondly, in Oracle all individual DML statements are atomic (i.e. they either succeed in full, or rollback any intermediate changes on the first failure) (unless you use the EXCEPTIONS INTO option, which I won't go into here).

If you wish a group of statements to be treated as a single atomic transaction, you'd do something like this:

BEGIN   SAVEPOINT start_tran;   INSERT INTO .... ; -- first DML   UPDATE .... ; -- second DML   BEGIN ... END; -- some other work   UPDATE .... ; -- final DML EXCEPTION   WHEN OTHERS THEN     ROLLBACK TO start_tran;     RAISE; END; 

That way, any exception will cause the statements in this block to be rolled back, but any statements that were run prior to this block will not be rolled back.

Note that I don't include a COMMIT - usually I prefer the calling process to issue the commit.

---

It is true that a BEGIN..END block with no exception handler will automatically handle this for you:

BEGIN   INSERT INTO .... ; -- first DML   UPDATE .... ; -- second DML   BEGIN ... END; -- some other work   UPDATE .... ; -- final DML END; 

If an exception is raised, all the inserts and updates will be rolled back; but as soon as you want to add an exception handler, it won't rollback. So I prefer the explicit method using savepoints.