Pragma autonomous transaction

[dilippanda]

Hi Experts,

I want to know what is the use of pragma autonomous transaction in PL/SQL.

In which condition should we use this?

Please guide me with an example.

Thanks,
Dilip

[debasisdas]

Hi
dilippanda
Welcome to TSDN.

You have reached the right place for knowledge shairing.

Here you will find a vast resource of related topics and code.

Feel free to post more doubts/questions in the forum.

But before that give a try from your side and if possible try to post what/how you have approached to solve the problem.

It will help Experts in the forum in solving/underestanding your problem in a better way.

Please follow the posting guidelines in every new post/reply.

[debasisdas]

The pragma instructs the PL/SQL compiler to establish a PL/SQL block as autonomous or independent.

You can put the autonomous transaction pragma anywhere in the declaration section of your PL/SQL block.Autonomous

Transaction is a feature of oracle 8i .when executed within an autonomous transaction, and you will have to include a COMMIT or ROLLBACK in your program.

With PRAGMA AUTONOMOUS_TRANSACTION , the transaction state maintained independently . Commit/Rollback of nested transaction will no effect the other transaction.

[debasisdas]

example #1
=============

CREATE OR REPLACE PROCEDURE prgtest IS
 
PRAGMA AUTONOMOUS_TRANSACTION;
 
BEGIN
INSERT INTO test (v1)  VALUES  ('value1t');
 
COMMIT;
END prgtest;
 

[dilippanda]

Hi,

Thanks for your useful input.
I would appreciate your input if you can explain me in which scenario should we use this pragma in our PL/SQL block.

Thanks,
Dilip

example #1
=============

CREATE OR REPLACE PROCEDURE prgtest IS
 
PRAGMA AUTONOMOUS_TRANSACTION;
 
BEGIN
INSERT INTO test (v1)  VALUES  ('value1t');
 
COMMIT;
END prgtest;
 

[Saii]

Hi,

Thanks for your useful input.
I would appreciate your input if you can explain me in which scenario should we use this pragma in our PL/SQL block.

Thanks,
Dilip

Generally we use autonomous transactions when we lneed to log any action in the porgram flow irrespective of the fact that main SQL executed successfully or not.