ORA-04091

code

Sounds familiar? A friend encountered it recently.

Mutating table problem from within a row level trigger on running a SELECT on the same table.

Did some digging around and figured out 2 ways of handling it :-

  • Use PRAGMA AUTONOMOUS_TRANSACTION
  • Break the trigger into 2 parts - a row level one and an “after operation” statement level trigger and use a PL/SQL table construct to pass data between the two.

No point elaborating on it here. Most Oracle Press texts recommend the same solution.

But wait! PRAGMA AUTONOMOUS_TRANSACTION means starting a fresh transaction. Do we really want to do that?

And the second approach essentially is a global variable approach. What about multi-user situations?

And since I have been in non-Oracle territory for some time; it is still taking some time for this to register!

And then; my friend comes back with this from AskTom and things start making a lot of sense. I won’t expound on it here and spare you (my non-existent reader) the pain of listening to yet another plagiarized version. In any case, the conclusions are there in the article for you to read and ponder.

As for me; I am left with a sense of intense frustration that titles from Oracle Press such as Oracle 10G PL/SQL Programming talk about the above listed approaches for handling the mutating table problem without making even a lame effort to list down the things to guard against as well as the side effects that these solutions introduce into the system!

Thank you Oracle Press! It was nice to have read yet another title from your esteemed stables which leaves the reader with half-baked and potentially destructive information.

I shall banish all your tomes to the back of my closet forthwith.