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.
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.
The default behavior of Commit PL/SQL block:
You should explicitly commit or roll back every transaction. Whether you issue the commit or rollback in your PL/SQL program or from a client program depends on the application logic. If you do not commit or roll back a transaction explicitly, the client environment determines its final state.
For example, in the SQLPlus environment, if your PL/SQL block does not include a COMMIT or ROLLBACK statement, the final state of your transaction depends on what you do after running the block. If you execute a data definition, data control, or COMMIT statement or if you issue the EXIT, DISCONNECT, or QUIT command, Oracle commits the transaction. If you execute a ROLLBACK statement or abort the SQLPlus session, Oracle rolls back the transaction.
https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqloperations.htm#i7105
BEGIN-END blocks are the building blocks of PL/SQL, and each PL/SQL unit is contained within at least one such block. Nesting BEGIN-END blocks within PL/SQL blocks is usually done to trap certain exceptions and handle that special exception and then raise unrelated exceptions. Nevertheless, in PL/SQL you (the client) must always issue a commit or rollback for the transaction.
If you wish to have atomic transactions within a PL/SQL containing transaction, you need to declare a PRAGMA AUTONOMOUS_TRANSACTION in the declaration block. This will ensure that any DML within that block can be committed or rolledback independently of the containing transaction.
However, you cannot declare this pragma for nested blocks. You can only declare this for:
Reference: Oracle
You don't mention if this is an anonymous PL/SQL block or a declarative one ie. Package, Procedure or Function. However, in PL/SQL a COMMIT must be explicitly made to save your transaction(s) to the database. The COMMIT actually saves all unsaved transactions to the database from your current user's session.
If an error occurs the transaction implicitly does a ROLLBACK.
This is the default behaviour for PL/SQL.
Source: Stackoverflow.com