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.
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
<span style="color: #000000;">SQL> CREATE OR REPLACE TRIGGER trig_ins_emp_log</span>
<span style="color: #000000;"> 2 AFTER INSERT ON employee</span>
<span style="color: #000000;"> 3 DECLARE</span>
<span style="color: #000000;"> 4 PRAGMA AUTONOMOUS_TRANSACTION;</span>
<span style="color: #000000;"> 5 BEGIN</span>
<span style="color: #000000;"> 6 INSERT INTO emp_log (operation_date, operation_description) VALUES ( SYSDATE, 'Insert</span>
<span style="color: #000000;"> 7 in employee table');</span>
<span style="color: #000000;"> 8 END;</span>
<span style="color: #000000;"> 9 /</span>
<span style="color: #000000;"> Trigger Created.</span>
<span style="color: #000000;">SQL> INSERT INTO employee VALUES ('E01', 'John Doe');</span>
<span style="color: #000000;"> 1 row created.</span>
It is simple, isn’t it? If you liked this post your comments are appreciable.
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 email@example.com. Again I appreciate your visit. Hope to see you again and again!