What are Autonomous transactions ? Give a scenario where you have used Autonomous transaction in your reports ?

An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction.
Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. So, you can log events, increment retry counters, and so on, even if the main transaction rolls back.

More important, autonomous transactions help you build modular, reusable software components. For example, stored procedures can start and finish autonomous transactions on their own. A calling application need not know about a procedure's autonomous operations, and the procedure need not know about the application's transaction context. That makes autonomous transactions less error-prone than regular transactions and easier to use.

Furthermore, autonomous transactions have all the functionality of regular transactions. They allow parallel queries, distributed processing, and all the transaction control statements including SET TRANSACTION.

Scenario : You can use autonomous transaction in your report for writing error messages in your database tables.

Interview Candidate
Aug 15th, 2005
1
4570

Basics

Answer

Showing Answers 1 - 1 of 1 Answers

Pawan Ahuja

Aug 28th, 2006

Hi to all,
autonomous transaction is transaction that is used for independentle commited
basically it is used in triggers ,procedures,
create or replace procedure ins_rec
pragma autonomous_transaction
begin
insert into aa1 values('pawan',11);
commit;
end;
step 2
begin
insert into aa1 values('hello',111);
ins_rec; ----call procedure
rollback;
end;
but here hello and 111 will be rollback.
but in ins_rec will be saved
regards
Pawan Ahuja