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.
Is this answer out of date? If it is, please let us know via a Comment