Pragma Autonomous_Transaction

Have you been in a situation where you need to audit your failed transactions? Technically speaking, have your manager asked you to log DML (Insert, Update or Delete) statements into the log table? Yes, then Pragma Autonomous_Transaction can be of assistance to you.

Taking a real world example, what if I want to know the count of number of times the ball of children playing cricket in front of my house struck my glass window? It does not concern to me whether the window is broken or not, but still I want to keep the record. Should I sit near the window daily while they play? Obviously No!

Imagine if I have a robotic assistant who could sit infinitely near that window and jot down every time the ball hits the window? This could help me out, right?

Pragma Autonomous Transaction works just like that assistant who will perform its pre-defined independent operation after specific events occurs.

Autonomous transaction in oracle leaves the context of current transaction, perform an independent transaction, fires a DDL statement (COMMIT or ROLLBACK) and return back to the original transaction.

Why PRAGMA?

PRAGMA here signifies that the statement is compiler directive. This statement will be processed at compile time and not at run time.

Business Use And Application of Pragma Autonomous_Transaction

Pragma Autonomous_Transaction is useful to perform logging or auditing transactions, keeping records of error logs and to track the count of DML statements fired on that database object.

How to Use Pragma Autonomous_Transaction

A trigger or procedure can be marked as autonomous by declaring it as PRAGMA AUTONOMOUS_TRANSACTION.

Application of Pragma Autonomous_Transaction

  • Stored Procedures and Functions
  • Anonymous PL/SQL Blocks
  • Packages

Example

It is simple, isn’t it? If you liked this post your comments are appreciable.

Subscribe here to stay tuned with regular updates of OracleMine.com.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *