I am in the process of building a system where I need to notify a Window Service in a .net based application in case of any change in the DB table (Insert , Update , Delete). I know this can be done with the ADO.NET Events in SQL. Is there any common solution for Oracle and SQL which can provide these kind of notifications. This needs to happen as and when the update happens on real time basis.
+2
A:
Both SQL Server and Oracle support Change Data Capture.
http://msdn.microsoft.com/en-us/library/bb522489.aspx
http://download.oracle.com/docs/cd/B10501_01/server.920/a96520/cdc.htm
You can capture the change and propagate it.
Also, there are third party products which will do replication between SQL Server and Oracle.
Raj More
2010-03-26 02:18:38
Is there any way we can notify to .net client from Data Capture event from Oracle?
2010-03-26 11:22:04
I believe you would have to POLL for changes on a periodic basis.
Raj More
2010-03-26 13:04:22
If you need a kind of change notification in your app, then Oracle's Change Data Capture IS NOT the technology you need. It's complex and should be used in data replication. Instead of it, use Data Change Notifications.
2010-03-26 16:00:46
+4
A:
The only active push technology for SQL Server is Query Notifications, which works on very platform specific ways, like SqlDependency. Oracle has an equivalent in Data Change Notifications, which is just as platform specific. There is no common layer abstraction between the two, nor is there any chance of one to appear any time, given the huge differences between the two implementations.
BTW, technologies like Change Data Capture are designed for keeping disconnected clients in sync (ie. Sync Framework) and they are basically Replication in disquise, they are not suited for data change client notifications.
Remus Rusanu
2010-03-26 02:24:09