Oracle database to send notification / Invoke a ja

Question

I have a table which has event-details. New records will be inserted to this table by another process continuously. The table has a column Event-end-time.

I have to design a mechanism to invoke a java method to process the events upon reaching the event end time. Some of the option i have are :

1. Continuously query the database (every 1 min) and find the next latest report to be processed and invoke it on reaching the end time.
2. Figure out if there is any built in mechanism in ORACLE database to send the java application a notification / Oracle itslef invoking the java method upon reaching the end time of the event.

I have seen options for notification if an record is Inserted/updated/deleted (trigger based) , but till now i have had no luck if the Notification condition to be made custom(if time specified in a column of teh record is reached).

Any advice regarding this will be much appreciated.

Regards, Deepak

Answer 1

/* removed trigger idea because the event end data is already in the table and the action * should be fired when the event date/time is reached */

Since you want to fire your event when a certain time is passed, it could simpley be adding a Oracle Scheduler job with a start date identical to the event end time. See CREATE_JOB Procedure on Oracle Documentation site. Oracle Scheduler is very powerfull and can start all kinds of actions like pl/sql code, procdures and shell scripts, that can be local and remote using credentials.

Answer 2

The best way that I know of to do this is via Oracle AQ ( a JMS implementation )

There is also database change notification, which I don't have any experience in, but could be what you are after, I believe it actually builds upon AQ.