Code Scavenger’s Chronicles

Sounds familiar? A friend encountered it today.

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 the last 5 years or so; 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 wont 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 putting these solutions in; introduces 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.

Maybe not having your support people write books will help?

Ah..Have you ever wondered how all these social networking sites show you, your friend’s friends and their distance from you? Not an easy problem if you have millions of users across geographies.

Case in point: LinkedIn. All user searches with a “name” return users and an indication (1st, 2nd, 3rd) of their distance from you provided they are at most 3 degrees away from you. What do they use? Good old graphs! 40 instances in production. About a 100Million+ edges! Wow..Wouldnt that be a good problem to work on?

What if you used a Object DB instead? Would it even have a fighting chance?

See this. Slides 19-20.

<yodaspeak>DIG. MORE. I MUST</yodaspeak >