advice sought: letting application know a table has been changed

Hi,

I was just wondering if anyone had any alternatives to this situation I have in my oracle app.

My application is given tasks to perform which cause data in the remote oracle database to change. Depending on the task completed, certain files in the filesystem where the application is run need to be updated (based on the changes that occurred in the database). At the moment, once a task is completed the application knows which files need to be updated and queues another task for itself to update those files. I'd like to move the code which knows which files need to be updated into the database as the procedures that are called know best what has changed and I'd like to separate the application which handles the tasks from the code (currently in the app) which updates the files in the filesystem.

An obvious way to do to this is for the procedures in the database handling the tasks to insert rows into a table saying what files need to be updated. A new second process outside the database can query this table to see what files need to be updated. However, because I have no idea of the frequency of inserts into this table this new outside process will need to loop keep doing selects on the new table to see if anything has changed. Some (perhaps many) of these selects will return no change and will be wasted.

Is there any other way I can get oracle to inform my application that an insert has occurred in a table other than the outside app continually selecting from the table? Perhaps a procedure which it can call which would not return until the table had changed (maybe knowing this via a trigger).

One last thing, the app is written in Perl. However, even if Perl's DBI and DBD::Oracle could not cope with this if it could be done via OCI I would be prepared to look at adding functionality to DBD::Oracle.

Thanks Received on Thu Oct 30 2008 - 12:28:27 CDT