Skip to main content

Posts

Showing posts from August 6, 2009

Question 12: What is PRAGMA AUTONOMOUS_TRANSACTION?

You are running a pl/sql block or procedure called parent In that procedure you called another one procedure namely child and that procedure contains commit statement. After calling the child procedure the transactions done in parent procedure are also committed due to the child procedure commit statement. But you want to rollback or commit the parent procedure whole transactions based on your requirement after the calling procedure child. In this situation, you should add the "pragma autonomus transaction" in child procedure. If you add this statement then it acts as a separate. If you use commit in child procedure it will not affect the parent procedure. Example : CREATE OR REPLACE PROCEDURE parent() AS BEGIN UPDATE tab1 SET fieldname = value WHERE condition; UPDATE tab2 SET fieldname = value WHERE condition; child(); UPDATE tab4 SET fieldname = value WHERE condition; IF parentcondition COMMIT; ELSE ROLLBACK; END IF; END. CREATE OR REPLACE PROCEDURE child() AS PRAGMA AUTONO