Autonomous Transactions

There are cases when you have to run a large script, that will influence your data (DML Only) but the situation of half work is impossible due to data consistency.

In Oracle it's very easy because when you make a change in your data (Insert, Update, Delete, Merge), Oracle will create a new transaction.
The changes will be limited to your session only (Read more in our Transaction post).

After running the first DML command, the following DMLs will stack in that transaction created for the first command, and all the changes are local in your session.

Transaction is terminate whether a Commit Or a Rollback is execute.
(DDL commands Commit the transaction).


Getting back to our script, This format is suitable:


BEGIN
<DML Command>
<DML Command>
<DML Command>
<DML Command>
<DML Command>
<DML Command>

COMMIT;

EXCEPTION WHEN OTHERS THEN
     ROLLBACK;
END;


This will ensure the script will either run completely or not at all.
But what if we want to keep track on the script? and report to a log table for each successful DML? (For example- how many rows were affected).
Ofcourse we can use the dopl (dbms_output.print_line()) and write directly to the console,  but that is too temporary.

Let's create a new Table for logging our script:

CREATE TABLE script_log (execute_time DATE,
                         dml_name VARCHAR2(100));

And now, we'll just insert the information to the log table.

Can you see the problem?
This insertion to the log table is a DML command itself, therefore it will be roll-backed each time an error occurred somewhere in the script and we lost the information.

We want a mechanism that allows us to use Commit on certain commands.

And thankfully Oracle supply this kind of mechanism  called - Autonomous Transaction.
This transaction is a child transaction of the original one, and can be committed without effecting the host transaction.
You can easily create a pl/sql paragraph that is autonomous like that:

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO sciprt_log(execute_time, dml_name)
    VALUES (sysdate, 'Insert into Employees');
  COMMIT;
END;

Or even create an autonomous function/procedure but remember that- an autonomous section has to end either with a commit or a rollback clause. You can leave that transaction open!

Good luck!