Infinite loop, autonomous transactions, jobs...
Date: Tue, 12 Oct 2010 04:11:12 -0700 (PDT)
Message-ID: <3484edc8-ed2d-472c-9d1b-a55b7a86b30c_at_l8g2000yql.googlegroups.com>
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
Received on Tue Oct 12 2010 - 06:11:12 CDT