Oracle Autonomous Transaction

Let me try to explain Oracle Autonomous Transaction in a little different way so that you can remember it very easily.

How many different types of plsql blocks can you write?

Ooops!!

Procedures and Functions are the pls/ql blocks written uniquely with or without parameters. What else could I do with this? What are all these different types available? Let us try it out.

1. We use to write a Procedure/Function without giving it a name for adhock purpose as we don't want to store that in the database to use it later. - ANONYMOUS PROC/FUNC

2. We use to use this anonymous items in declaration section of plsql as well - LOCAL PROC/FUNC

3. When we need this proc/func to be stored in database to use frequently, we store it with a name tagged to it- STAND ALONE PROC/FUNC or say stored Procedure or Function.

4. To logically group our procs/funcs we can place these procedures or functions inside a package - PACKAGED PROC/FUNC

So now we know there are 4 different ways we can use procedures and functions.
We can use all the above mentioned different ways of proc/fncs for autonomous transactions.

Autonomous Transactions?????

I had heard about Autonomous Institutes we're they function independently.
PL/SQL autonomous transaction also work exactly the same. They function independently. It helps you leave the calling transaction and performs an individual transaction and resume to the calling transaction without affecting the calling transaction.

Confused? No need to worry. You will get it by the time you complete this.

Here calling transaction refers to your main PL/SQL block and the autonomous transaction is called from this block. The purpose of your main PL/SQL block and autonomous PL/SQL block is entirely different. Both behave as transaction done is seperate sessions. There is no link between both these type of transactions, hence only committed data can be shared among them.

Autonomous transaction code block is nothing but plsql blocks. Syntax is as below,

Example for anonymous:

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
Statement;
COMMIT;
END;
/

Example for stored PROC:
CREATE OR REPLACE PROCEDURE PROCEDURE_NAME AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
statement;
COMMIT;
END;
/

We have 5 different type of plsql blocks which can be used for this purpose. Four of them are those discussed above and the fifth one is "TYPE methods".

Let us try to understand autonomous transaction a little more deep by going through few scenarios.

1. You need to insert two records into an empty table.

INSERT INTO temp_table (id, name) VALUES (1, 'Scott');
INSERT INTO temp_table (id, name) VALUES (2, 'Peter');

Next without committing this transaction you want to execute an autonomous transaction, say an individual transaction.

Before executing the autonomous transaction, select count(*) give you result: 2

SELECT count(*) FROM temp_table;

Execute autonomous transaction which inserts 5 records to the same table and have a commit statement within this autonomous transaction code block.

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FOR i IN 3 .. 5 LOOP
INSERT INTO temp_table (id, name)
VALUES (i, 'Name ' || i);
END LOOP;
COMMIT;
END;
/

Now select count(*) give you result as expected:7

Now execute a rollback.

Rollback;

Select count(*) gives you only 5 as the result.

The records inserted in current transaction got rolled back. The commit affected only the autonomous transaction block.

Hope now, you are clear with how to user autonomous transaction.

Now the question is why should we use autonomous transaction?
It is most popularly used for error logging. In applications particularly like banking, you can't commit a transaction until it is successfully completed. Hence to capture any error that may happen in between any transaction we use autonomous transaction.

How can we use this?
Create a proc as autonomous which logs data into one table. Call this proc in the exceptional section of your plsql block before you rollback.The error data is committed successfully by the autonomous transaction and your calling block actions are rolled back

Restrict the use of autonomous transaction only for error logging, else it will be a disaster.

Hope you understood well. Regards Shiyas..