通过ORACLE 访问（读写） SQL SERVER （求购）

Question

不同数据库平台的互连一般称之为数据库的异构服务，各厂商的具体实现技术不一样：
在SQL SERVER里面叫做LINKED SERVER，通过ODBC实现与其它数据库的互联。 
而ORACLE实现异构服务的技术叫做透明网关（Transparent Gateway）。目前ORACLE利用透明网关可以实现和SQL SERVER、SYBASE、DB2等多种数据库的互联。
    Oracle透明网关软件在Oracle 8i时是需要花钱另买的，大约1万$；到Oracle 9i时是作为数据库的一个组件免费发布的。安装时在组件种选择即可。（默认是不安装的。）第一步：
    安装透明网关for sql server的软件后，可以在$ORACLE_HOME下看到tg4msql目录，编辑$ORACLE_HOME/tg4msql/admin/inittg4msql.sql文件有一行：HS_FDS_CONNECT_INFO="SERVER=192.168.0.1;DATABASE=pubs"
其中:SERVER=192.168.0.1是SQL SERVER的服务名，后面的DATABASE不言而谕，是数据库名。
第二步：
修改 listener.ora
在SID_LIST中加入以下内容：
(SID_NAME = tg4msql) # SID自己命名 
(ORACLE_HOME = c:) 
(PROGRAM = tg4msql) 
第三步：
在tnsnames.ora中填加
sql2k = 
(DESCRIPTION = 
  (ADDRESS_LIST = 
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521)) )
  (CONNECT_DATA = (SID = tg4msql) )
(HS=OK)
 ) 
第四步：
现在你就可以SELECT * FROM TABNAME@sql2k 或
UPDATE TABNAME@sql2k  SET ... WHERE ...注意：
ORACLE SERVER的初始化参数global_names设为false

Answer 1

在SQL server 2000的企业管理器中的安全性中有一“LINKED SERVER”（链接服务器），SQL Server提供的图形化界面是很容易配置一个“LINKED SERVER”的。当然，也可以用命令（系统存储过程sp_addlinkedserver 和 sp_addlinkedsrvlogin ）来实现。

Answer 2

1 如何配置透明网关呢？打开inittg4msql.ora:
#
# HS init parameters
# xzh代表MS SQLSERVER服务名，pubs代表要访问的MS SQLSERVER数据库
#
HS_FDS_CONNECT_INFO="SERVER=xzh;DATABASE=pubs"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
2 监听的配置oracle_home\network\admin\Listiner.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = xzh)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = xzh)(PORT = 1527))
      )
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = xzh.world)
      (ORACLE_HOME = D:\oracle\ora92)
      (SID_NAME = xzh)
    )
    (SID_DESC = 
       (GLOBAL_DBNAME =MySQL) 
       (PROGRAM = tg4msql) 
       (SID_NAME = MySQL)
       (ORACLE_HOME = D:\oracle\ora92)
    )
  )
加亮部分代码是要在监听文件中手工添加，GLOBAL_DBNAME、SID_NAME可以任意指定，PROGRM必须指向tg4msql 如图2.1所示。
3 本地服务文件的配置oracle_home\network\admin\TnsNames.ora
XZH =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xzh)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xzh.world)
    )
  )TG4MSQL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xzh)(PORT = 1527))
    )
    (CONNECT_DATA =
      (SID =MySQL)
    )
    (HS = OK)
  )
上例中MS SQL SERVER的本地服务名指定为TG4MSQL，可以任意写，SID必须是Listiner.ora中指定的那个SID_NAME，PORT也必须是监听中指定的PORT，主机等都要符合一致。到此为止，我们把透明网关大致已经配置好啦，如果要访问MS SQL SERVER数据库，还要在ORACLE中创建数据库链才行。
4 在MS SQLSERVER中创建登录帐号xzh口令xzh，授权使之可以访问PUBS数据库。
SQL>CREATE  DATABASE  LINK  FROM_SQL  CONNECT TO xzh IDENTIFIED BY xzh USING ‘TG4MSQL’
USING ‘TG4MSQL’中的 ‘TG4MSQL’是tnsnames.ora中配置好的指定MS SQL SERVER服务器的可以被ORACLE访问的本地服务名，FROM_SQL是我们以后要引用的数据库链名，切记MS SQL SERVER中的登录帐号（xzh/xzh）必须符合ORACLE的帐号规范，如果用（xzh/0000）就会在创建数据库链路时失败。
SQL>SELECT * FROM sales@FROM_SQL
如果有数据返回，表示我们访问SQL数据库的通道已经建成，请大胆使用吧，但对远程异构数据库不充许进行DDL操作。
SQL>CREATE TABLE all_users@TG4MSQL FROM all_users;
ERROR 位于第 1 行:
ORA-02021: 不允许对远程数据库进行 DDL 操作
二 MS SQL SERVER访问ORACLE的方法
1 通过行集函数OPENDATASOURCE
  SELECT  *  FROM OPENDATASOURCE( 'MSDAORA',
    'Data Source=xzh.oracle;User ID=POS;Password=POS')..POS.A0325
MSDAORA是OLEDB FOR ORACLE的驱动，初始化字符串中必须提供要访问的ORACLE本地服务名、用户名、口令。然后引用表中数据时要以服务器..用户名.表名的格式。注意一定是四部分组成，用户名与表名一定要大写，服务器与用户名之间是两个点号。
SELECT * INTO PUBS.DBO.A0325 FROM 
OPENDATASOURCE( 'MSDAORA',
         'Data Source=UITE;User ID=POS;Password=POS' )..POS.A0325
说明：将ORACLE服务器UITE中POS模式的A0325导入到MS SQLSERVER的PUBS数据库，注意A0325在这条语气执行时被创建，如果A0325已经存在PUBS数据库中，可以使用INSERT INTO的语法导入数据
INSERT INTO PUBS.DBO.A0325 FROM 
OPENDATASOURCE( 'MSDAORA',
         'Data Source=UITE;User ID=POS;Password=POS' )..POS.A0325
 2 通过MS SQLSERVER的链接服务器访问ORACLE的数据
   --在查询分析器中查看已经存在的链接服务器
   SELECT＊FROM SYSSERVERS
   --添加链接服务器ORCL到MS SQL SERVER，也可以在企业管理器中创建
   EXEC SP_ADDLINKEDSERVER
   @server = 'ORCL',                    --ORCL是SQL中链接服务器名称
   @srvproduct = 'Oracle',　　　　　　　　--Oracle        固定的　　　　　　　　　　　　
   @provider = 'MSDAORA',             --MSDAORA   固定的
   @datasrc = 'UITE'                    --DataSrc      本地服务名
   
   EXEC SP_DROPSERVER ‘ORCL’       --删除链接服务器
   
在这里声明一下，如果现在就访问ORACLE肯定不能，因为没有提供登录ORACLE的用户名/口令
EXEC SP_ADDLINKEDSRVLOGIN 'ORCL', false, 'sa', 'POS', 'POS'
SA是MS SQLSERVER的登录帐号，POS/POS是ORACLE的登录帐号。
SELECT * FROM ORCL..POS.A0325
FROM后面必须是四部分组成。为什么执行后报错呢，本人一直在寻找解决办法，最后发现通过T-SQL语句没法解决，只有打开MS SQLSERVER的企业管理器。
 
图2.2 配置ORACLE的远程帐号
 
图2.3设置远程登录帐号
SELECT * FROM ORCL..POS.A0325   
通过图2.2、2.3以后，在查询分析器执行时，立即从ORACLE的ORCL服务器上POS模式的A0325表中查到数据，注意在图2.3步操作过程中，必须在安全性页面选中“用此安全上下文进行”的复选项，然后在“远程登录/使用密码”所对应的文本中输入ORCL服务器上有登录权限的帐号。
SELECT * FROM OPENQUERY(ORCL, 'SELECT * FROM POS.A0325')
3 使用MS SQL SERVER的OPENROWSET函数
SELECT A.*
FROM OPENROWSET('MSDAORA',
   'UITE';'POS';'POS', POS.A0325) AS A ORDER BY A.ID
说明：有些地方使用行集函数要用别名才能引用，请大家注意。
４使用ODBC的方式
SELECT A.*
FROM OPENROWSET('MSDASQL','ORCL_ODBC';'POS';'POS',
   POS.A0325) AS  A  ORDER BY A.ID
说明：ORCL_ODBC是访问ORACLE 服务器UITE的ODBC数据源，以上所有代码在SQL QUERY ANALYZE 中通过。关于MS SQL SERVER访问ORACLE有四种方式，其中通过ODBC与链接服务器的方式做起来难度较大，对于初学者会复杂一些，使用OLE DB FOR ORACLE的驱动时，有OPENDATASOURCE与OPENROWSET函数可以使用，都很方便，笔记喜欢使用后者，交待一下，在数据存取方面，OLE DB效率较ODBC为佳。

Answer 3

在实施这样的系统给一条建议,采用SQL Server访问Oracle的方法进行操作,不要采用Oracle访问SQL Server的方法操作。两种都做过，后一种是种非常痛苦的事情

Answer 4

hollyzp 你好！可否进一步说说后一种为什么“是一件非常痛苦的事情“？
如果做一段程序，作中间的协调工作，是否会好些？还望诸位大虾指点。

Answer 5

从Oracle环境下访问SQL Server数据库的时候,从我的实际经验来说有以下非常不方便的地方:
1.语句书写复杂。
  从Oracle访问SQL Server时，SQL语句中涉及到的SQL Server中的字段必须用双引号括起来，而且区分大小写。对于SQL Server表中定义字段的时候如果没有统一成大写，或者小写，或者严格的遵循了一定的规则，那么在Oracle环境下编写SQL语句就非常麻烦了，需要到SQL Server库中确认。
2.需要手动调节执行计划。
  如果在Oracle中多表关联SQL Server中的表，需要手动定制执行计划，否则性能可能非常低。
3.Oracle 的DbLink本身存在bug。
  已经发现了一个很严重的bug，在执行计划不同的情况下，查询的数据会不准，本来只有5条结果，查出了100多条，Oracle公司已经确认了这个错误。另外，若join多个表（一般三个表以上），会莫名其妙的报错，等。当然，如果必须要从Oracle中访问SQL Server数据的话，建议先在SQL Server中建立视图，或者在Oracle中建立临时表缓存数据，这样可以有效的避免一些摸不着头脑的错误。