SQL Developer:从已连接的SQL Server DB和已连接的Oracle DB联接表 [英] SQL Developer: Joining tables from connected SQL Server DB and connected Oracle DB

问题描述

我们正在从SQL Server 2005迁移到Oracle 11G.我一直在尝试使用存储在SQL Server表中的数据对Oracle中的表进行更新.

We are migrating from SQL Server 2005 to Oracle 11G. I have been trying to do an update to a table in Oracle using data stored in a table in SQL Server.

使用SQL Developer，我已完成以下操作:

Using SQL Developer, I have completed the following:

1. 创建与Oracle数据库的连接

1. Created a connection to the Oracle Database

Created Connection name --> e9_crp
Supplied User name and Pass
Defined Host Name and SID

我还创建了到包含参考数据的SQL Server的连接

I also created a connection to the SQL Server that contains the reference data

Connection Name --> sql_data
User Name and Pass
Host and Port

用于SQL Server的连接名称为sql_data

The connection name used for SQL Server is sql_data

用于Oracle的连接名称为e9_crp

The connection name used for Oracle is e9_crp

SQL Server中的表位于具有dbo所有者的数据库my_tmp中，并称为tiers(my_tmp.dbo.tiers).

The table in SQL Server is in a database my_tmp with owner of dbo and is called tiers (my_tmp.dbo.tiers).

在SQL Developer中，我可以查看，选择和查看层表.在sql_data连接中，此SQL语句也可以在工作表中使用:

In SQL Developer, I can see, select, and view the tiers table. This SQL statement also works using the worksheet when in the sql_data connection:

 select * from [my_tmp].[dbo].[tiers]

在e9_crp连接架构中尝试连接到该表时，我一直在尝试执行以下操作:

Trying to connect to this table when in the e9_crp connection schema, I have been trying to do something like this:

 select * from [sql_data].[my_tmp].[dbo].[tiers]

但是这将返回一个错误，指出tiers表不存在:

But this returns an error stating that the tiers table does not exist:

 ORA-00903: invalid table name
 00903. 00000 -  "invalid table name"

两个表的ID均为"item"，因此在理想情况下，该表应该有效:

Both tables have a unique ID of 'item', so in a perfect world this should work:

 select a.itm, b.tier
 from [e9_crp].[crpdta].[itemmaster] a inner join [sql_data].[my_tmp].[dbo].[tiers] b
     on(a.itm = b.itm)

这是假设我在表标识符中使用连接名称.但是，这显然行不通.我需要做的是能够根据需要以类似的方式将这两个数据库连接在一起.

This is assuming that I use the connection name in the table identifier. BUT, this obviously does not work. What I need to do is be able to join these 2 databases together in a similar fashion as necessary.

如何使用SQL Developer联接这两个表?我为联接尝试了表字符串的多次迭代，但是没有运气.感谢您的帮助.

How can I use SQL Developer to join these 2 tables? I have tried multiple iterations of table strings for the join but no luck. Any help is appreciated.

推荐答案

Oracle具有DBLink的概念，而SQL Server具有Linked Server的概念.这两个功能使您可以从两个单独的数据库中查询数据.

Oracle has the concept of a DBLink, and SQL Server has the concept of a Linked Server. Both these features let you query data from two separate databases.

这里有一个链接，其中包含有关设置到SQL Server DB的Oracle DBLink的说明...

Here's a link with instructions for setting up an Oracle DBLink to a SQL Server DB...

http://www.dba-oracle.com/t_database_link_sql_server_oracle.htm

这是一个链接，其中包含有关将SQL Server链接服务器设置为Oracle DB的说明...

And here's a link with instructions for setting up a SQL Server Linked Server to an Oracle DB...

http://support.microsoft.com/kb/280106

另请参阅此SO帖子...

See also this SO post...

加入Oracle和MS SQL Server的SQL语句

这篇关于SQL Developer:从已连接的SQL Server DB和已连接的Oracle DB联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！