Oracle table change monitor

Question

I have a java application that is connected to a view on a remote Oracle db.

Does anyone know of a way in Java to monitor this table for changes? I.e. if there are inserts of updates etc I would need to react.

Answer 1

Look at Oracle Change Notification, a so interesting Oracle feature.

From the Oracle documentation: "Database Change Notification is a feature that enables client applications to register queries with the database and receive notifications in response to DML or DDL changes on the objects associated with the queries. The notifications are published by the database when the DML or DDL transaction commits."

Answer 2

You can place a INSERT/UPDATE/DELETE trigger on the table to perform some action when 'data' changes are made to the table. (as opposed to changes to the structure of the table)

I believe 10g also supports triggers on views.

but I'm not sure how you can notifiy the java process of this other then by polling.

sorry.

you could possibly create some solution where the java app has a 'listen' server and the database pushes it back a message. but that sounds hard to maintain.

Justin Cave in the comments suggests you could configure Oracle Streams to send out logical change records (LCRs) that a Java app could subscribe to via JMS. Or the trigger could write records to an Advanced Queue that Java could subscribe to via JMS.

you will still need to be wary of Oracle transations.. due to the way Oracle transactions work, the trigger will fire on the change, but it may also fire multiple times..

and in anycase the java app would not bee able to 'see' the changes until a commit had been performed.