Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, Shimmy.

Asked: September 09, 2016 - 8:28 pm UTC

Last updated: September 10, 2016 - 1:03 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hello,

What are all real practical purpose/code have you seen people use with AUTONOMOUS_TRANSACTION?(Other than auditing)

AUTONOMOUS_TRANSACTION can start multiple transactions in the same session, I am trying to understand what people use it for as part of GOOD coding practice.

I have seen people use it for populating LOOKUP tables when they do a SELECT and as part of that, if lookup value is missing, they insert the missing lookup value.
For example, if a SELECT statement tries to fetch employee no for "Chris" and "Chris" is missing from Emp table, it calls a AUTONOMOUS_TRANSACTION function to insert the missing emp record. It may or may not be buggy code. I think a good code would populate all missing employees and then only do a SELECT on the lookup table(in this case EMP)

Thank you

and Connor said...

I agree they are perhaps *overused* but the common cases I see are:

- auditing (as you said)
- error logging (eg log the error, then raise the error to rollback the real work)
- status logging ("I've done 1000 of 10000 pieces of work - a bit similar to way the v$session_longops works)

In the lookup table example, I've seen them in stateful environments (like Oracle FOrms etc), where the current transaction is still in progress, and we need to add (say) a new reference value and return the current transaction.

In the stateless model (web pages etc), its less common because you have the save the state of the current "transaction" regardless.

Others welcome to share their usages.

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.