有哪些选项可用于从Oracle数据库连接到Microsoft SQL Server数据库? [英] What options are available for connecting to a Microsoft SQL Server database from an Oracle database?

问题描述

此刻，我使用自定义的JDBC连接器从远程MS SQL Server数据库中提取数据.效果很好，但感觉不怎么样.

At the moment I pull data from remote MS SQL Server databases using custom-built JDBC connectors. This works fine but doesn't feel like the way to do it.

我觉得我应该能够将JDBC连接字符串放入服务器上的tnsnames中，并使它正常工作".我已经对该功能进行了四处浏览，但似乎不存在.

I feel I should be able to put a JDBC connection string into tnsnames on the server and have it "just work". I've looked around a little for this functionality but it doesn't seem to be there.

通过这种方式，我只需使用数据库链接就可以连接到几乎所有数据库.

In this way I could connect to pretty much any database just using a database link.

我错过了什么吗?

看起来这两个选项是通用连接"和"Oracle网关"，但令我惊讶的是所有这些.通用连接随附数据库许可证，Oracle网关是附加组件.对于Generic Connectivity，如果您在Linux(如我)上运行，则需要获取ODBC驱动程序，因为它没有与数据库捆绑在一起.

It looks like the two options are Generic Connectivity and Oracle Gateways but I'm surprised that's all there is. Generic Connectivity comes with the database license and Oracle Gateways is an add-on. For Generic Connectivity, if you're running on Linux (like me) you need to get hold of an ODBC driver as it isn't bundled with the database.

但是...由于Oracle是Java的狂热爱好者，并且数据库中内置了JVM，所以我认为基于JDBC的链接技术将是轻而易举的事情.在TNSNAMES中具有JDBC连接字符串似乎是自然的扩展，并且一切都会正常".

However... with Oracle being such keen Java fans, and with a JVM built-in to the database I'd have thought a JDBC-based linking technology would have been a no-brainer. It seems a natural extension to have a JDBC connection string in TNSNAMES and everything would "just work".

有人知道为什么这个功能不可用吗?

Anyone any ideas why this isn't available?

推荐答案

Generic Connectivity is what you are after, it will let you setup a remote database link against MS SQL Server, so you can do queries like

select * from mytable@my_ms_sql_server;

我只在针对MySQL的Oracle 9i中使用了它，发现在我们的例子中，它不能很好地工作，因为它最终消耗了大量的ram，我们仍然使用它，但是现在只是使用它来同步到本地表，而不是对其进行实时"查询.但是，它可能与MS SQL Server和10g/11g完全不同

I've only used it in Oracle 9i against mysql, and found, that in our cases, it didn't work very well, as it ended up using up MASSIVE amounts of ram, we still use it, but now just use it for syncing to a local table rather than doing 'live' queries against it. BUT, it might be completely different against MS SQL Server, and in 10g/11g

这篇关于有哪些选项可用于从Oracle数据库连接到Microsoft SQL Server数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！