Hello Friends,Can you guys suggest me that what is autunomous transcation, its types and what is its use? If anyone know then please tell me the real time scenerio.
Thanks in Advance!
Autonomous transaction is independent transaction. It is used in procedures,triggers. This is used in declare section of the procedure.
Ex.create or replace procedure p1 is Pragma autonomous_transaction;BeginInsert into t values('india');Commit;End;
BeginInsert into t values('hyd');P1;Rollback;End;Select *from t;O/p:india
AT means independent transaction, they are 4 types,1.autonomous transaction2.pragma restrict reference3.pragma serial reusable4.pragma exception in itIt is mainly used in declaration part and TCL command we can use.
Autonomous transaction is an separate transaction from the on going transaction. It is used when we want to commit, rollback a particular set of transaction. An example of this is consider auditing of log report. Whenever some exceptions occur you want to record the details of that exception by storing into a table. If you simple insert records in auditing table and execute commit, then that commit will effect the overall transaction. In this case developers actually create a procedure for recording an exception. That procedure will have an 1)Autonomous transaction declared in the declare section of the code. 2)DML operations that need to perform. 3)Commit or rollback keyword to save the changes of that particular transaction.
This will help you make your change permanent without actually committing your overall transaction.
Autonomous transaction is a pragma in oracle. Pragma is a keyword in oracle which will send the instructions to the compiler. There are 5 types of pragmas in oracle. 1. Pragma Autonomous Transaction. 2. Pragma Restrict reference, 3. pragma serially resuable 4. prama exception init 5. pragma inline.
Pragma Autonomous Transaction will help you when you are having multiple dml transactions in our procedure/function. each transaction will work individually without getting any disturbing. Other wise your working on same object with multiple dml operations, you will get Mutating table error. To avoid this situation, we have to use pragma autonomous transaction in our pl/sql block. And you have to declare this pragma in the declarative section. All pragmas you have to declare in declarative section only. In interview perspective .. how to avoid mutating error in oracle? you have to tell to avoid pragma autonomous transaction. It will occur in trigger/function. Mutating error message is .. table <table name> is mutating..function/trigger may not be see in it. 04091 error code. (HYD and INDiA). Hope u understand now.
Pragma autonomous transaction keeps the every transaction independently.
Hello Jyoti can you tell me what Pragma restrict reference means?
Suppose any package contains the several number of procedures if any transaction is done ,u want commit only particular procedure in that package with out disturbing the others this is done through autonomous transaction
Sign up for STechies
Ask Question From Our Experts