Hi Folks,
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.
Thanks in advance Len
+4
A:
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.
ShoeLace
2008-12-03 14:45:26
Depending on how heavy the architecture needs to be, you can 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.
Justin Cave
2008-12-03 14:54:05
Brilliant thanks that looks like the ticket
LenW
2008-12-03 14:55:30
The problem is that Streams works at the object level, not sure you can configure Streams to send out LCRs for a view. Is the view such that you can identify a handful of table(s) to subscribe to changes on and then poll the view to see if the row you're interested in really changed?
Justin Cave
2008-12-03 15:07:13
While it is true that triggers could get fired multiple times with a rollback in between, Oracle AQ/ Streams is transactional, so the enqueue action would be rolled back as well. The only issue would be if you did something that cannot be rolled back in the trigger (i.e. send email).
Justin Cave
2008-12-03 15:09:45
+3
A:
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."
FerranB
2008-12-03 14:47:40
Seems like it only works for PL/SQL or C callbacks but thats pretty much the stuff I need thanks - now how to wire it up to the java app ... :)
LenW
2008-12-03 14:53:43
I believe that Oracle can call Java stored procedures, I just don't know how to do it. http://www.oracle.com/technology/sample_code/tech/java/jsp/oracle9ijsp.html might help, though.
R. Bemrose
2008-12-03 15:46:52
+1
A:
- What version of Oracle?
- Are you trying to monitor a table for changes? Or a view?
- What sort of change volume are you anticipating?
- What sort of response time do you need?
- Are you trying to architect a solution for a new system (i.e. you're free to do whatever you like on the Java side and on the Oracle side)? Or are you trying to work around limitations in an existing system (i.e. there are constraints that you can't touch a third party's database, etc.)?
Justin Cave
2008-12-03 14:50:33
- Oracle 10g- Actually its a synonym over a view- volume of changes is low but num records is > 20 million- response time no critical within a few minutes is fine- java side is mine but the oracle view is provided as-is
LenW
2008-12-03 14:52:40