oracle getway实现oracle访问sqlserver

oracle  10.2.0.4  IP:192.168.51.42  AIX
sqlserver 2005 192.168.51.85  DB:test

安装的getway是11.2.0.1的，装在windows上 ip192.168.51.178

现在我在getway服务器上先配置好 inittg4msql.ora
HS_FDS_CONNECT_INFO=sqlserverIP,1433//LCR
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

其中HS_FDS_CONNECT_INFO=sqlserver Ip,prot//sqlserver_DB

这是sqlserver2005的写法

然后同样在getway server上配置listener
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = getway Ip)(PORT = 1521))
    )
  )
SID_LIST_LISTENER =
      (SID_DESC =
         (SID_NAME = dg4msql)
         (ORACLE_HOME = D:\product\11.1.0\tg_1)
         (PROGRAM = dg4msql)
    )
  )
重启listener

然后在oracle server上配置tnsnames.ora
MSSQL =
  (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = getway Ip)(PORT = 1521))
    (CONNECT_DATA =
      (SID = dg4msql)
    )
    (HS = OK)
  )

 在oracle server上创建dblink

sql>create public database llnk ms_sql connect to sa identified by sa using "MSSQL";

sql>select * from t1@ms_sql;

但是利用

select x from t1@ms_sql

可能会出现ora-00904的错误

select sVENDOR  log_Board_Parts@ms_sql;
出错，
error:
ORA-00904: "sVENDOR": invalid identifier

方法：每个字段加上"",并区分大小写即可

select "sBoard","sReelId" from log_Board_Parts@ms_sql where "sBoard" ='N0259FBDC49CCNS0S4';

参考文档:

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=466267.1

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24849178/viewspace-719775/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24849178/viewspace-719775/