Tom, I wouldn't go so far as to characterize this use of PRAGMA AUTONOMOUS_TRANSACTION as a "very dangerous option". I think that is too strong of a statement. I use it quite often in this context, especially in the use of TABLE functions. Normally my INSERTS for the trigger (and below) are limited to logging functions. Instead I would say that this use of PRAGMA AUTONOMOUS_TRANSACTION requires a good knowledge of Oracle transaction management.
Regards, Mike On Wed, Nov 4, 2009 at 8:17 AM, Thomas Olszewicki <[email protected]> wrote: > > On Nov 4, 8:54 am, Vlad <[email protected]> wrote: > > My requirement is that I have an existing function that is used very > > infrequently by a single function in my application. I have another > > existing stored procedure. > > > > I want the stored procedure to be executed every time a query uses the > > database function. > > > > I appreciate my requirements may sound a bit illogical but I am > > constrained by a number of factors - I'm kind of trying to put a > > square peg through a round hole. > > > > Let me know if this still doesn't make sense and I'll have another go. > Vlad, > Your function can call other functions and procedures > as long as none of them updates any table. > In essence a SELECT statement cannot change the state of the data. > There is an work around to use: > PRAGMA AUTONOMOUS_TRANSACTION; > but it is a very dangerous option. > HTH > Thomas > > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
