replication / caching SQL Server data on Oracle

For performance reasons, we replicate a small subset of a remote database on our Oracle instance (10.2 W2k3). Currently the remote database is Oracle and the replication is happening smoothly (and has for a decade or so).

We just found out that the remote data is being migrated to a SQL Server database, so we'll need to find a different way to do the replication.

The current replication method is rather "stone-age" - once a day we run a PL-SQL package that deletes the data, and then re-inserts from the remote database via a database link. This has worked for over a decade - it's a fairly small dataset, and a one-day latency is acceptable.

One option is to implement a database link to SQL Server and tweak the PL-SQL so that the select clause will run on SQL Server. Suggestions for other, better approaches cheerfully appreciated.

Thanks

//Walt Received on Mon Dec 05 2011 - 10:51:20 CST