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

On Oct 30, 6:09 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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.

Thanks, will go away and read up on this.

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

Yes, I considered this but the daemon process(es) are running on different machines to oracle
and I did not want to get into remote file stuff.

> HTH -- Mark D Powell --

Thanks again. Good pointers.

Martin Received on Thu Oct 30 2008 - 14:25:14 CDT