Re: Infinite loop, autonomous transactions, jobs...

>
> As a fellow who used to post here used to say, your application is
> going to hell.

Without a stop on purgatory, I guess.

> You need to understand how oracle handles multiversioning and
> optimistic writes.  I can predict without seeing your code that you
> will have severe performance problems, as recursive undo tries to keep
> all this straight, and your object segments swiss cheese from your
> delete craziness.  Not to even mention strangeness from the tool,
> since which tool is unknown - but some of them do extremely poor
> things, like make a new connect for each statement, or oh-so-helpfully
> try to undo Oracle's transaction model.

There isn't a code... yet. I have basic understanding on undo and rollbacks and all this... Thus, I don't want to write something that will hog my server on a useless infinite loop. That's why I asked... :) I have no idea on how the vendor do his thing. He even wanted me to make "select where rownum=1" inside the loop "to make sure you are processing one register a time" and I had to fight him to defend my cursor-for-loop method. I still think that an infinite loop is something that not only has to be avoided, it shouldn't be even thought of! Well, I'm aware how dumb this sound, but I don't know how his app is inserting on that transfer table, if it was Oracle both sides I could easily use triggers because I'd know that it would work no problems, but he is saying his other customer tried and had big retention problems and on this matter I assume he knows more than me.

> On the other hand, I wrote an app that does similar things with a
> couple of flags, it works most of the time, but still has some curious
> bugs having to do with when things commit.  Fortunately, those (losing
> a few seconds here or there) are far overshadowed by human errors
> (letting things go on for hours that they should have stopped,
> scanning wrong barcode, odd requirements, etc.).  It's volume isn't
> particularly high anyhow, and it doesn't delete (so far).  Not
> deleting has saved some peoples butts, and allowed for some analysis
> not originally envisioned.

I have more than plenty of disk space and I don't like to delete things if I don't really have to. I forecast weird problems on this app too. God help me...

>
> You need to go buy a Tom Kyte book, understand how to do autonomous
> transactions correctly, and maybe google on the things Tom Kyte wishes
> had never been added to Oracle because nearly everyone does it wrong.
> Theoretically, it could work.  http://www.oracle-base.com/blog/2006/02/28/autonomous-transactions/http://www.orafaq.com/node/1915 http://lmgtfy.com/?q=autonomous+transactions+tom+kyte

Well, autonomous transaction saved me in the past... It doesn't look that it will save me now.

What if I use a trigger to raise a dbms_schedule to run once, say, a few milliseconds after the table gets the row, one for each row I get from the transfer table? Does it sound *too* insane? This way I'd be completely isolating the receiving procedure from the procedures that would perform the data parsing.

Btw, I had seen this first article (lol, teenage daughters, that ring a bell) a couple hours ago and some of Tom's articles. Thanks for the others.

I was also looking into dbms_cds_* but too bad: my Oracle ain't enterprise... :(

Question of the day is "how do I have a procedure that will run whenever a row is inserted on a table and do process the row in a complete separate isolated manner of the transaction that inserted the row?" I just can't figure that out...

>
> You can't even theoretically go live without issues without serious
> load testing.  Come on!

I think I didn't made myself clear, I fully understand your "come on", but I will try: No matter how big and comprehensive and load intensive my tests are, nothing will replace thousands of real world transactions an hour 24/7/365. Pushing 5 thousand records once is not the same as one thousand every hour for months in a row. Can you tell I'm slightly scared? I guess I will end up trying everything and seeing what is bad and what not. Oh, there goes my deadline...

Thank you! I really enjoyed the lmgtfy website, that's really cool! And thanks for you insight, much appreciated.

• Tiago