Oracle autonomous transaction example

I. Usage of autonomous transactions

Audit that cannot be rolled back: Generally, when a trigger is used to prohibit operations such as table updates, if logs are recorded, the trigger will cause log rollback when an exception is thrown. Autonomous transactions can prevent this point.

Avoid variant table: the table that triggers the trigger in the trigger.

Using ddl to write data to a database in a trigger: the stored procedures or functions that perform write operations insert, update, delete, create, alter, and commit on the database cannot be called simply using SQL, you can set it as an autonomous transaction at this point, avoiding errors such as the ora-14552 being unable to perform ddl, commit, rollback in a query or dml, And the ora-14551 being unable to perform dml operations in a query. Note that the function must have a return value, but only the in parameter cannot have an out or in/out parameter ).

Develop more modular code: in large-scale development, autonomous transactions can make the code more modular. failure or success does not affect other operations of the caller. The cost is that the caller has lost control of this module, in addition, the module cannot reference data not submitted by the caller.

Ii. Oracle self-made transactions

Oracle self-made transactions refer to stored procedures and functions that can handle internal transactions independently from external transactions. They are declared using pragma autonomous_transaction to create an autonomous transaction, you must use the PRAGMA AUTONOMOUS_TRANSACTION statement in PL/SQL at the top of an anonymous block or in the definition of stored procedures, functions, data packets, or triggers. The SQL statements executed in such a module or process are autonomous.

To end an autonomous transaction, you must commit a commit, rollback, or execute ddl; otherwise, an Oracle error ORA-06519 is generated: active autonomous transaction detected and rolled back.

Iii. Instances

 
 

  
  
-------------------------------------------------------------------------
  
  
-- Stored procedure name: P_DTMS_UPDATE_SAP
  
  
-- Insert data to T_DATA_DTMS_TAX_EXPORTTOSAP
  
  
-- Autonomous transaction pragma autonomous_transaction)
  
  
-- 2012-12-28
  
  
-------------------------------------------------------------------------
  
  
Create or replace procedure P_DTMS_UPDATE_SAP (
  
  
I _pkvalue in number,
  
  
I _opcontent_ori in VARCHAR2,
  
  
I _opcontent_dest in VARCHAR2,
  
  
I _source in varchar2
  
  
)
  
  
Is
  
  
Pragma autonomous_transaction;
  
  
Begin
  
  
Insert into T_DATA_DTMS_TAX_EXPORTTOSAP (ID, DTMS_TAX_INVOICE_ID, OPERATE_TYPE, EXPORT_TO_SAP_ORI, EXPORT_TO_SAP_DEST, OPERATE_TIME, SOURCE)
  
  
VALUES (SEQ_DATA_DTMS_TAX_EXPORTTOSAP.NEXTVAL, I _pkvalue, 'update', I _opcontent_ori, I _opcontent_dest, sysdate, I _source );
  
  
Commit;
  
  
End;
  
  
-------------------------------------------------------------------------
  
  
-- Trigger name: TRG_INVOICE_EXPORTTOSAP_MODIFY
  
  
-- Triggered when the table T_DTMS_TAX_INVOICE is updated. It is used to change 1 to 0 for the EXPORT_TO_SAP flag, throw an exception, and roll back the modification. That is, it is not allowed to change EXPORT_TO_SAP from 1 to 0.
  
  
-- 2012-12-28
  
  
-------------------------------------------------------------------------
  
  
Create or replace trigger "TRG_INVOICE_EXPORTTOSAP_MODIFY"
  
  
After update
  
  
On T_DTMS_TAX_INVOICE
  
  
For each row
  
  
Declare v_pkvalue NUMBER (20 );
  
  
V_opcontent_ori VARCHAR2 (50); -- the value before modification
  
  
V_opcontent_dst VARCHAR2 (50); -- modified value
  
  
Begin
  
  
V_pkvalue: =: new. id;
  
  
Case when updating then
  
  
V_opcontent_ori: =: old. EXPORT_TO_SAP;
  
  
V_opcontent_dst: =: new. EXPORT_TO_SAP;
  
  
If v_opcontent_ori = 1 and v_opcontent_dst = 0 then
  
  
P_DTMS_UPDATE_SAP (v_pkvalue, v_opcontent_ori, v_opcontent_dst, 'invoice '); -- an autonomous transaction that is called to perform operations independently of its parent transaction.
  
  
RAISE_APPLICATION_ERROR (-20100, 'Could not Modify T_DTMS_TAX_INVOICE.EXPORT_TO_SAP From 1 To 0. '); -- throw an exception. RAISE_APPLICATION_ERROR (num, msg) and num are between-20000 and-20999. msg writes the exception you want to throw.
  
  
End if;
  
  
End case;
  
  
End;
 
 

Link: http://ithead.iteye.com/blog/1756605

 

Edit recommendations]

1. How to use Java stored procedures in Oracle (details)
2. A long journey to migrate DB2 to Oracle
3. 11 important Database Design Rules
4. 10 suggestions for making the database faster
5. 20 database design best practices