Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003

    Unanswered: Autonomous Transaction problem...

    could anyone tell me the advantage of Autonomous Transaction that uses in PL/SQL?
    Thanx in advance!
    Rgd xujb,

  2. #2
    Join Date
    Sep 2003
    The Netherlands

    the advantage of the AUTONOMOUS_TRANSACTION, is that is it working like if it was another transaction/session. It is based upon the principle of the consistent view that oracle creates for each session, so when using autonomous transaction, you do not see changes made (but not yet committed) in other session.
    For example : you have a procedure to update certain rows in a table. On the other hand, you want auditing whenever an attempt was made to change the values in the table, not only when a DML action succeeds, but also when it doesn't. If you put the auditing code in a database trigger, it will not record anything when a DML action failed. Storing the code in a procedure with the pragma autonomous transaction, will always record the auditing info, because the procedure can commit and take place without committing the transaction of updating the column.
    Beware : the procedure with the pragme autonomous transaction MUST contain a COMMIT or ROLLBACK. Otherwise you will recieve errors.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts