plsql_autonomous [My wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
=====Overview=====
Autonomous transactions are used when you want a certain transaction to happen without another transaction and you don't want it to affect the first transactions. Let that sit for a second......ok, so what I mean here.

Let's imagine you start a transaction A. While you process that one, you start other transaction B which will finish prior the transaction A. Now, if you COMPLETE transaction B, that WILL complete also transaction A. Why?....Well because a transaction completes either with:

  * Commit
  * Rollback (which also ends with commit but that is a different story :) )

So, let's say we commit transaction B, well that will commit also transaction A, EXCEPT if transaction B isn't AUTONOMOUS transaction :) If Transaction B is defined as autonomous, then its completion WON'T affect transaction A.

So, when we know that, how we define an autonomous transaction:


=====Define=====
Defining an autonomous transaction is done with the key word: "PRAGMA" as follows:

<Code:PL/SQL|Defining Pragma transaction>
CREATE OR REPLACE PROCEDURE log_msg
  (p_act_id accounts.act_id%TYPE,
   p_msg VARCHAR2) IS
   PRAGMA AUTONMOUS_TRANSACTION;                     <- This line indicates autonomous transactions
BEGIN
insert into log_table(log_id,...)
.....
.....
COMMIT;
END log_msg;
</Code>

=====Usage=====
Autonomous transactions can be used withinb other functions, triggers or even anonymous blocks as follows:

<Code:pl/sql|Usage within a procedure>
Create or replace procedure process_order(p_act_id accounts_act_id%TYPE,
                                          p_item_id items.item_id%TYPE,
                                          p_item_value items.item_value%TYPE) IS
BEGIN
--Debit Account
Update accounts....
--Log message
log_msg(1,'Act 1 debited');             <- AUTONOMOUS TRANSACTION
....
COMMIT;
....
END process_order;
</Code>

Autonomous transactions are generally used in the following scenarios:
  * Logging
  * Usage Counters
  * Retry Counters
  * Provide Modular Independent Code