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

SQL>   CREATE OR REPLACE TRIGGER trig_ins_emp_log
  2                  AFTER INSERT ON employee
  3      DECLARE
  4                  PRAGMA AUTONOMOUS_TRANSACTION;
  5      BEGIN
  6                  INSERT INTO emp_log (operation_date, operation_description) VALUES ( SYSDATE, 'Insert
  7                  in employee table');
  8      END;
  9      /
  Trigger Created.

SQL> INSERT INTO employee VALUES ('E01', 'John Doe');
  1 row created.

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.

 

 

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.