Re: advice sought: letting application know a table has been changed

On Oct 30, 1:28 pm, Martin <martin.j.ev..._at_gmail.com> wrote:
> 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

The dbms_alert package can be used to signal a waiting program that a specific action has taken place. It can also pass short messages to the waiter.

You can find documentation on this package in the PL/SQL Packages and Types manual for 10g or the Supplied Packages and Types manual for 9.2

Other possibilities are using the dbms_pipe package or utl_file to output information to daemon type programs.

HTH -- Mark D Powell -- Received on Thu Oct 30 2008 - 13:09:54 CDT