Trigger to DBMS_PIPE Gotcha's

  I have written a ProC application with a function to dynamically generate a trigger on a table and write the results to dynamically named DBMS_PIPE which is being polled by the same application. The ultimate customer of the data is a realtime factory control system. My goal is to create a way to subscribe to data events in our "legacy" systems that use Oracle. My application works great in a test environment, but before I take it to my customers I need to be able to describe how I have addressed all the gotcha's out there, and how I won't be killing the performance of their Oracle based applications.

The triggers that are generated simply read the data from either the :new or :old records formatting a string as it goes and writing the data to the pipe. The triggers are "after" triggers, so I am not trying to get in the middle of transactions.

My Pro C reader is a single threaded tcl program. It polls each pipe it is listening to one at a time and forwards the results to the realtime system. The polling period is 1 second. There is a 2 second delay between polls if no data was found on the previous poll. I poll in a loop rather than block because the reader must also be somewhat responsive to asynchronous requests for new triggers to be set up.

If I have lots of triggers, say 5 - 10, would it be more or less efficient to have 5 -10 pipes or just one pipe, from the Oracle side.

What ever I do has to work with Oracle 7.3.x and be OS independent. I built it on an HP and haven't thought about NT yet.

I'm not a dba or expert with oracle at all so I expect to be missing something.

C-

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 23 1999 - 23:42:28 CEST