Can I set up materialised views on oracle for a SQL Server table over sqllink?

Question

Hi,

I have a table in a SQL Server database that needs to be visible to an oracle database. We have tried using a normal view over sqllink, but we are not able to create an onUpdate triggers on that view.

I have read that we can create the trigger if it is a materialised view, but is unable to find any information on whether it can be done across different databases. (all the example are for oracle to oracle tables)

Can someone tell me if it is possible? and what issues I might need to look out for if I used materialised views?

Thanks

Answer 1

I do something similar and ended up using Oracle's HS gateway to handle it. Created an MV based of tables or queries to establish a staging area in Oracle. Followed up with logic to meet requests.