3

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

iWantSimpleLife
  • 1,944
  • 14
  • 22
  • Can you explain a bit more. You can read a SQL Server table from Oracle using a Database link. You can create a view in Oracle that does that. SQL Server isn't aware of the Oracle link, so it shouldn't prevent any triggers on the SQL Server end. – Gary Myers Jun 26 '10 at 00:32
  • The main problem I am encountering is that the connection between Oracle and SQL Server does not allow it to be shared between connections. My application uses multiple connections to connect to Oracle to query from the view (hence multiple connection over the database link, hence FAIL!) So now, we are investigating if we can set up an "on update" trigger on the view (that links to the SQL Server table) to copy the records to another Oracle table. That way, we can query it to our hearts content without hitting the shared connection problem. – iWantSimpleLife Jun 28 '10 at 08:59
  • Plan was dropped as my engineer discovered some problems with the approach. We were unable to create the view using fast refresh, meaning that the view will need to copy all the records over when needed, and we could not get the view to refresh on update or select, so we need to manually issue a view refresh statement. – iWantSimpleLife Jul 15 '10 at 09:16

1 Answers1

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.

iggy
  • 11
  • 1