如何自动将数据从SQL Server推送到Oracle？
我让用户在 SharePoint 中输入数据（在 SQL Server 上运行），但显然，用于查看该数据的应用程序将是在 Oracle 上运行的 Oracle Apex 应用程序。如何将数据自动推送到 Oracle 数据库？
I have users entering data in SharePoint (Running on SQL Server), but my application to view that data will be an Oracle Apex app running on Oracle, obviously. How do I have the data be pushed into the Oracle db automatically?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
首先，您确定需要将数据复制到 Oracle 吗？ Oracle 异构服务 允许您创建数据库Oracle 中使用 ODBC 连接到非 Oracle 数据库的链接（假设您使用免费的 ODBC 透明网关）。然后，您的 APEX 应用程序可以通过发出在数据库链接上运行的查询来查询和报告 SQL Server 中的数据。 Tim Hall 在 配置异构服务。
如果确实需要复制数据，您可以在 Oracle 中创建物化视图，使用通过异构服务创建的数据库链接查询 SQL Server 中的对象，并安排这些物化视图定期刷新。不过，物化视图需要进行完全刷新，这意味着每次刷新时都需要将每一行从 SQL Server 复制到 Oracle。这通常会限制您实际进行刷新的频率。如果需要将数据复制到 Oracle 数据库，并且需要发送增量更改以便 Oracle 端不会落后太远，可以使用 从非 Oracle 数据库到 Oracle 数据库的流，但这涉及很多更多工作。
First off, are you sure that you need to replicate the data to Oracle? Oracle Heterogeneous Services allows you to create a database link in Oracle that connects to a non-Oracle database using ODBC (assuming you use the Transparent Gateway for ODBC which is free). Your APEX application could then query and report on data that is in SQL Server by issuing queries that run over the database link. Tim Hall has a good article (though it's a bit dated and some of the components have been renamed, the general approach is still the same) on configuring Heterogeneous Services.
If you do need to replicate the data, you can create materialized views in Oracle that query the objects in SQL Server using the database link you created with Heterogeneous Services and schedule those materialized views to refresh on a regular basis. The materialized views will need to do a complete refresh, though, which means that every row will need to be copied from SQL Server to Oracle every time there is a refresh. That generally limits the frequency with which you can realistically have refreshes happen. If you need the data to be replicated to the Oracle database and you need to send incremental changes so that the Oracle side doesn't lag too far behind, you can use Streams from a non-Oracle database to an Oracle database but that involves a lot more work.
在 SQL Server 中，您可以设置链接服务器，以允许您查看其他数据库的数据。您可能会看到 Oracle 是否有类似的东西（如果不一样的话）。或者，您可以使用 SQL 的集成服务将数据推送到 Oracle 表。不幸的是，我只知道如何在 SQL Server 中设置链接服务器，而且我没有太多使用 ssis 的经验来告诉你如何做到这一点，但这些是我能想到的前两个选项，你可以进一步探索。
这是我发现可能也有帮助的链接： http://www.dba-oracle.com /t_connecting_sql_server_oracle.htm
In SQL Server you can setup linked servers that allow you to view data from other db's. You might see if Oracle has something similar, if not the same. Alternatively, you could use the sql's integration services to push the data over to an oracle table. Unfortunately I only know how to setup linked servers in SQL Server and I don't have a lot of experience with ssis to tell you how to do that, but those are the first two options I can think of that you might explore further.
Here's a link I found that might be helpful as well: http://www.dba-oracle.com/t_connecting_sql_server_oracle.htm
据我所知，没有办法“自动”地在 DBMS 上工作。像 Sql Server Integration Services 这样的 ETL 工具可能会有所帮助，但将会有一个加载延迟（因为它必须轮询更改）。您可以在 SharePoint 数据库表上构建一些更新触发器，但这将变成支持噩梦。
There's no way to do it "automatically" that I know of that will work across DBMS. ETL tools like Sql Server Integration Services might help but there's going to be a loading delay (as it will have to poll for changes). You could build some update triggers on the SharePoint database tables but that's going to turn into a support nightmare.