I have an external non-oracle application that is set to insert rows
on a table on my oracle (10.2.0.5) database. It does that to integrate
a system that does not use databases with our forms/reports based
system.

The vendor insists on creating a procedure that is on an infinite loop
and processing the new rows as they come into the table. He said not
to use triggers in any way, because a trigger could slow down the
performance and cause the other system to lose messages. The infinite
loop is always select'ing two tables: the one that is updated by the
external system and other that has a column with a value of 0 or 1, if
1, goes on, if 0, breaks the loop. I know I could use a pipe
(dbms_pipe), but I feel this is just complicating what is badly
designed.

Problem is that this setup hangs the entire session and sometimes it
is hanging the SQL Developer session that is trying to query this
table. What I was thinking: either creating a trigger calling an
autonomous transaction or creating a trigger that would submit jobs
(usually I'd rather use dbms_scheduler instead of dbms_job). Both ways
I would not prevent the external application to insert the new rows
and would not have an application on an infinite loop. To my objection
of creating an infinite loop, vendor suggested starting the infinite
loop within a run-once job...

Has anyone faced a problem similar to this? If so, can you please
advise with your experience? As always, time constraints prevent me to
going full scale testing, I have to choose an architecture in the next
few couple weeks and present it to our developer team...

I thank you very much if you can give me any insight, thanks a lot!

best regards,
Tiago

On Oct 12, 7:11*am, Tiago <diariodastril... (AT) gmail (DOT) com> wrote:
Vendors often have no idea of how to properly design the database
usage portion of their application.

How are the inserts done, single row at a time or bulk? You would not
want to use row level triggers if direct load operations are used to
insert the data but if single insert statements are used the trigger
should probably not be an issue though I guess if would depend on
exactly what the trigger does.

How does the infinite loop process the data? That is, does it use
select for update? Does it delete the rows when it processes them?
How does it know the data is new or already processed?

Since the logic is getting into what appear to be hung situations have
you ran a trace on the process? This may provide you will a clue to
what is going wrong and how to fix it.

HTH -- Mark D Powell --

On Oct 12, 10:01*am, Mark D Powell <Mark.Powe... (AT) hp (DOT) com> wrote:

Even worse is when they are most used to MS Sql Server...


Now even the guy who is here to implement the thing knows. What I know
is that the application is multi-db enabled. It doesn't look java-ish,
but I can be wrong. .NET perhaps? Have no idea. It connects using a
string similar to the jdbc one.

The trigger would parse the rows and treat them as "messages". The
first column is the "message identifier" and upon the identifier, the
Oracle app will have to do different things, but in essence, it will
validate the data and if correct, insert it on the application
tables.

There is a column that the external app sets as "0". My job is setting
it as "1" when I pick the record and set as "2" when I finish. If
there are finished records with "1", that means that the record wasn't
properly processed. Vendor says it is best if I delete them "improve
processing speed" he says. There is no select for update.

I didn't try the trigger, because vendor said another if his customers
tried it, on Oracle, and there were problems because while the trigger
was being processed, the external app wasn't able to insert new rows
and that caused them many problems, then I suggested that the trigger
to call different procedures with pragma autonomous transaction, one
procedure for each message type... What I am afraid is that on test
environment I won't be able to reproduce a high load usage and may not
face the locks the vendor mentions. When this thing go to production
it can't go wrong, this is a critical operation and theoretically
should go live without issues...


Thanks very much for you help and insight!

best regards,
Tiago

On Oct 12, 7:14*am, Tiago <diariodastril... (AT) gmail (DOT) com> wrote:
My crystal balls are cracking together, they know what will happen...

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

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.

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.

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...-transactions/
http://www.orafaq.com/node/1915 http://lmgtfy.com/?q=autonomous+transactions+tom+kyte

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

jg
--
@home.com is bogus.
http://www.montrealgazette.com/news/...380/story.html

On Oct 12, 2:09*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:

Without a stop on purgatory, I guess.



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.

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...

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...

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

On Tue, 12 Oct 2010 07:14:00 -0700, Tiago wrote:


That is why there is the 2nd amendment in the constitution.



--
http://mgogala.byethost5.com

On Oct 12, 1:11*pm, Tiago <diariodastril... (AT) gmail (DOT) com> wrote:
Sounds interesting. I would suggest you tell him how to do his job
exactly as many times as he tells you how to do your job.

Just do it as you plan it (after doing the necessary homework) and
test it. He will always have new ideas but it is your ... on the line.
Insist on not letting the system go live without proper testing, you
can simulate any load you want. And if their system loses data, whose
problem is that exactly?

Krisztian

On Oct 12, 8:11*am, Tiago <diariodastril... (AT) gmail (DOT) com> wrote:

ok, I have given up on autonomous transaction and creating a miriad of
jobs. All I *don't* want is to do infinite loops.

I found that Advanced Queuing works on Oracle SE. Great!? Well, not
so.
I followed Tom Kyte's post on aq line by line, letter by letter. I can
enqueue. I can manually dequeue. But it does not register the pl/sql
function call back. I can see it on sys.reg$.

I have job_queues set to 10. I even had aq_tm_processes set to 10 even
that I don't need it. The thing doesn't call the registered pl/sql
callback procedure.

Am I missing something?

This is my inspiration source:
http://asktom.oracle.com/pls/asktom/...76 0267539329

10.2.0.5

Can anyone point me to a working example, known to work on 10r2?

-- Tiago
-> sometimes it's great being a developer....