AW: What do you use autonomous transactions for?

In short terms: Forget about it, won't work;)

Stefan

-----Urspr=FCngliche Nachricht-----
Von: Tony Davis [mailto:tony_at_apress.com] Gesendet: Dienstag, 17. Februar 2004 11:04 An: oracle-l_at_freelists.org
Betreff: RE: What do you use autonomous transactions for?=20

Hi,
=20

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

=20

"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."

=20

Cheers,

=20

Tony.

=20

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

=20

Hi

=20

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

What if part of your work done within the trigger is=20

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

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

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

changed and issuing a second AT to undo your work.

That might be a real performance problem, depending of=20

how often your triggers are executed and what kind of=20

work they're supposed to do.

=20

Stefan

=20

-----Urspr=FCngliche 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?=20

=20

=20

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

to

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

=20

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

logging

errors before rollback, etc.

=20

Dave

=20

> -----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?=20

>=20

>=20

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

> auditing are the

> primary uses of user created anonymous transactions.

>=20

>=20

> -----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?=20

>=20

>=20

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

> tables. I do not

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

> What have you used them for? Anything interesting?=20

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

> 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

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

>=20

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

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

=20

=20

=20

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

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
-----------------------------------------------------------------


=20
----------------------------------------------------------------
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
-----------------------------------------------------------------