What is pragma autonomous transaction? Plz can u let me know it in a simple understandable way.Thanks in advance.Byee

Showing Answers 1 - 27 of 27 Answers

missoracle

  • Oct 17th, 2006
 

Pragma Autonomous transaction:-

       -- When ever we commit.. all the transactions before the last commit in that session get saved. Suppose that u hav a senario u jst want a particular transaction only 2 be save.Inorder to achive that u make this as a seperate transaction which can b commited but prior to this the txns are not commit.

eg:

     being

       insert into tab1 () values ();

       update tab2 set values.. some more stmts..

         pragma sutonomus transaction

                  insert into tab3 values();

                commit;

       here.. insert into tab1 is only saved but not tab1 and tab2.

  

 

sangeeta

  • Oct 19th, 2006
 

what she means is only tab3 will be committed and not tab1 and tab2.

she made a spell mistake.

  Was this answer useful?  Yes

hello friend

 pragma autonomous transcation is nothing set of sql statements that transcation is differant from parent transcation

consider one anynomous block in that you are calling procedure

 declare

a number;

begin

insert into x values(12,30);

---know iam calling procedure here--

procedurename();

---rollback transacation--

rollback;

end;

above transcation is parent transacation...if u rollback the transcation takes place in procedure is also get rollback...to that not happen

in procedure we are  making use pragma autonomous transcation that is differant from parent transcation

pragma keyword tells compiler forcibily to do that....

in procedure

create or replace procedure basu

pragma autonomous_transcation;

as

begin

insert into y values(20,'xxx');

commit;

end basu;

this transcation is not rollback because its differant from parent transcation

  Was this answer useful?  Yes

autonomous transaction means independent commited

undersatnd by ex:

1.)Create or replace procedure auto_trans

is pragma autonomous_transaction;

begin

insert into table ee1(1,'pawan');

commit;

end;

now suppose again i enter the value by another pl/sql block

begin

insert into table ee1(2,'ahuja')

autonomous_transaction;

rollback;

end;

 

first block will be commit and other will be rollback;

 

Regards

Pawan Ahuja

Technical consultant

  Was this answer useful?  Yes

pragma autonomous transaction:

It is used for commiting the particular block only.

for e.g if u declare pragma autonomous transaction

suppose there are two insert statement in your Code and if the two insert fails it will go to the exception part for insertion and in the exception part there is one more insertion and then u commit. it will commit all the insert stmt which should not happen.

for commiting only the exception part pragma autonomous transaction is used.

--Suresh Sajwan

  Was this answer useful?  Yes

shinehere

  • Feb 25th, 2008
 

     An autonomous transaction is a transaction that is started within the context of another transaction, called the parent transaction, but is independent of it.

    An autonomous transaction can be committed or rolled back regardless of the state of the parent transaction.

   For making a transaction autonomous, we should use the PRAGMA

   PRAGMA AUTONOMOUS TRANSACTION

   A PRAGMA is a compiler directive, which in this case tells the compiler that this transaction is to be considered as AUTONOMOUS.

  Was this answer useful?  Yes

lijina

  • Nov 15th, 2011
 

before that statement that is pragma auto_tra only will save ...

  Was this answer useful?  Yes

pragadeesh

  • Apr 19th, 2012
 

Very simple meaning is...
1) Pragama means force (like it says to compiler forcible do this operation)

2) It is used for committing the particular block.

3) We can't rollback this Pragama autonomous block

  Was this answer useful?  Yes

kitti

  • Dec 19th, 2015
 

An autonomous transaction is an independent transaction initiated by other transaction
-When an autonomous transaction is called the parent transaction is currently suspended.
-We can commit or rollback an autonomous transaction independent of the parent transaction or without affecting the parent transaction.

  Was this answer useful?  Yes

sujata

  • Jan 19th, 2016
 

Pragma AUTONOMOUS_TRANSACTION instructs the compiler to treat the PL/SQL block following the pragma as autonomous (independent) from the calling transaction. Pragma can be applied on:-
1) Anonymous PL/SQL blocks
2) Local, standalone, and packaged functions and procedures
3) Methods of a SQL object type
4) Database triggers

By using Pragma we can commit or roll back operations, without committing or rolling back the data in the main transaction. for ex:

Code


  1. PRAGMA AUTONOMOUS_TRANSACTION


  2.  

  3.     INSERT INTO tttable(id,DATE)

  4.     VALUES         (id,SYSDATE);

  5.  

  6.     COMMIT; -- must commit or rollback

  7.  




Next, we have another transaction that calls this procedure.
Code

  1.  

  2.     DELETE tttable;

  3.     log_msg(Deleting ALL details);

  4.     ROLLBACK;

  5.     log_msg(after ROLLBACK);



After this, table would be unchanged, but there would be 2 (new) rows in the tttable table.

  Was this answer useful?  Yes

Habib Ali

  • May 10th, 2016
 

PRAGMA is instruction to compiler
AUTONOMOUS TRANSACTION means running an SQL transaction inside a PLSQL block of code independently i.e Making changes to DB regardless of other transactions & their locks/latches

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions