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博客

oracle getway实现oracle访问sqlserver

最新文章