RE: What do you use autonomous transactions for?

Hi,  

Apparently, it is not recommended to use autonomous transactions to avoid mutating table errors, as documented in Metalink Note:65961.1:  

"As all database changes are part of a transaction, if a parent has modified data, but not committed it at the point the autonomous transaction begins, then those modifications are not visible to the child...It is important to remember that if a trigger is running as an autonomous transaction, although it still has access to :OLD and :NEW values as appropriate, it does not see any rows inserted into the table by the calling transaction. Thus, using an autonomous trigger to obtain a maximum value currently in the table...[in other words, a mutating table scenario]...is unlikely to work."  

Cheers,  

Tony.  

-----Original Message-----
From: Stefan Jahnke [mailto:Stefan.Jahnke_at_bov.de] Sent: 17 February 2004 09:21
To: 'oracle-l_at_freelists.org'
Subject: AW: What do you use autonomous transactions for?  

Hi  

That's a good idea. I think the only drawback here is:

What if part of your work done within the trigger is

ok, before you encounter a problem. You can't rollback

what you've done in your AT then. You would have to do

a "roll-your-own"-rollback by keeping track of what you

changed and issuing a second AT to undo your work.

That might be a real performance problem, depending of

how often your triggers are executed and what kind of

work they're supposed to do.  

Stefan  

-----Ursprüngliche Nachricht-----

Von: David.Schmoldt_at_gazettecommunications.com

[mailto:David.Schmoldt_at_gazettecommunications.com]

Gesendet: Dienstag, 17. Februar 2004 00:29

An: oracle-l_at_freelists.org

Betreff: RE: What do you use autonomous transactions for?    

I *think* I read you can use autonomous transactions in table triggers =

to

avoid some mutating table situations. Haven't tried it myself.  

I do use them a lot in logging the progress of long-running jobs, =

logging

errors before rollback, etc.  

Dave  

> -----Original Message-----

> From: Powell, Mark D [mailto:mark.powell_at_eds.com]

> Sent: Monday, February 16, 2004 1:29 PM

> To: 'oracle-l_at_freelists.org'

> Subject: RE: What do you use autonomous transactions for?

>

>

> I would think that error message logging, debugging, and

> auditing are the

> primary uses of user created anonymous transactions.

>

>

> -----Original Message-----

> From: oracle-l-bounce_at_freelists.org

> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Ryan

> Sent: Monday, February 16, 2004 2:21 PM

> To: oracle-l_at_freelists.org

> Subject: What do you use autonomous transactions for?

>

>

> The only thing I have used them for is logging dbms_jobs to

> tables. I do not

> want the transaction to commit, but I want to track my progress.

> What have you used them for? Anything interesting?

> ----------------------------------------------------------------

> Please see the official ORACLE-L FAQ: http://www.orafaq.com

> ----------------------------------------------------------------

> To unsubscribe send email to: oracle-l-request_at_freelists.org

> put 'unsubscribe' in the subject line.

> --

> Archives are at http://www.freelists.org/archives/oracle-l/

> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

> -----------------------------------------------------------------

> ----------------------------------------------------------------

> Please see the official ORACLE-L FAQ: http://www.orafaq.com

> ----------------------------------------------------------------

> To unsubscribe send email to: oracle-l-request_at_freelists.org

> put 'unsubscribe' in the subject line.

> --

> Archives are at http://www.freelists.org/archives/oracle-l/

> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

> -----------------------------------------------------------------

>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.

--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

-----------------------------------------------------------------

 

 

 

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.

--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------