Introduction to Oracle Autonomous TRANSACTION (autonomous transaction)

Introduction to Autonomous TRANSACTION (Autonomous affairs)
In the process of doing some projects based on the lower version of Oracle, sometimes there are some headaches, such as the need to record some information to the trace table for each step of DML when executing the current transaction (transaction) consisting of multiple DML, because of the atomicity of the transaction, The submission of these trace information will be determined by the commit or rollback of the master transaction. This makes it more difficult to write the program, and the programmer has to log the trace information into an array-like structure, and then put them into a tracking table after the main transaction is finished. Oh, what a nuisance!
Is there an easy way to solve a similar problem?
Oracle8i's autonomous TRANSACTION (autonomous business, the following at) is a good answer.
At is a transaction that is called by the main transaction (the following MT) but is independent of it. When the at is invoked to execute, MT is suspended, and within at, a series of DML can be executed and commit or rollback.
Note that because of the independence of at, its commit and rollback do not affect the performance of Mt. At the end of the at execution, the master transaction gains control and can continue execution.

How does the definition of at be implemented? Let's take a look at its syntax. It's actually very simple.
Simply add the pragma autonomous_transaction to the following PL/SQL Declarations section.
1. Top-level anonymous PL/SQL blocks
2. Functions or Procedure (declaration or Declaration of Independence is available in the package)
3. Methods for SQL Object type
4. Trigger.
　　
Like what:
Declare at in a separate procedure

 create  or  replace  procedure   Log_error (error_msg  in  varchar2  (100   is   PRAGMA Autonomous_transaction;  begin  insert  into  error_log  (Sysdate,error_　　 msg);　　 commit  ;  end ; 

Let's look at an example, (Win2000 Advanced Server + oracle8.1.6, connect as Scott)
Create a table:
CREATE Table MSG (msg varchar2 (120));
First, write an anonymous PL/SQL block with a normal transaction:

DeclareCNT Number:= -1;--} Global variables  procedureLocal is  begin    Select Count(*) intoCnt frommsg; Dbms_output.put_line ('Local: # of Rows is' ||CNT); Insert  intoMsgValues('New Record'); Commit; End;begin  Delete  frommsg; Commit; Insert  intoMsgValues('Row 1');  Local Select Count(*) intoCnt frommsg; Dbms_output.put_line ('main: # of Rows is' ||CNT); rollback;  Local Insert  intoMsgValues('Row 2'); Commit;  Local Select Count(*) intoCnt frommsg; Dbms_output.put_line ('main: # of Rows is' ||CNT);End;

Run results (note open serveroutput)
Local: # of rows is 1--sub-program local can ' see ' uncommitted records in the main anonymous block
Main: # of rows is 2--the main anonymous block can ' see ' 2 records (they are all dropped by local commit)
Local: # of rows is 2--sub-program local first ' see ' 2 records, then commit the third record
Local: # of rows is 4--sub-program local and ' see ' the newly added records (they are all dropped by local commit), and then commit the fifth record
Main: # of rows is 5, the main anonymous block finally ' see ' All records.
From this example, we see that the location of commit and rollback will affect the entire current transaction, either in the main anonymous block or in the subroutine.
Now use at to rewrite the procedure local in the anonymous block:

    ... procedure  is     pragma autonomous_transaction;     begin     ...

Re-run (note open serveroutput)
Local: # of rows is 0--sub-program in local cannot ' see ' The uncommitted record in the main anonymous block (because it is independent)
Main: # of rows is 2, the main anonymous block can ' see ' 2 records, but only one is commited.
Local: # of rows is 1--sub-program local can ' see ' its previous commit record, but the records in the main anonymous block have been rollback in advance.
Local: # of rows is 3--sub-program local can ' see ' 3 records including primary anonymous block commit
Main: # of rows is 4, the main anonymous block finally ' see ' All records.
Obviously, at is independent, and when it executes, MT is paused. The commit,rollback of at does not affect the execution of Mt.
At the time of use, there are some considerations, briefly listed as follows:
1. In an anonymous PL/SQL block, only the top-level anonymous PL/SQL blocks can be set to at
2. If at attempts to access resources that are controlled by MT, deadlock may occur.
3. Package cannot be declared at, only function and procedure owned by the package can be declared as at
4. The AT program must end in commit or rollback, otherwise it will produce Oracle error Ora-06519:active autonomous transaction detected and rolled back
In the process of development, if the full use of autonomous transaction characteristics, will be able to achieve a more effective effect.

Introduction to Oracle Autonomous TRANSACTION (autonomous transaction)