oracle和sqlserver互訪

??????????????????????????????????????

by HuiYi_Love from ITPUB

oracle和sqlserver互訪!

前幾天由於工作的原因查找了oracle中查找sqlserver數據的資料,現測試成功,整理一下貼出!

要求:從Oracle中能取SqlServer的數據
環境:
OracleDb: Linux + Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production IP:192.168.1.52(TOPPROD)
MSQLDB: Windows2000 + SqlServer2000 IP:192.168.1.50(ERPSQL),測試用戶:sa/pass 測試數據表:EK.ACPTA
網關: WindowsXp + Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production IP:192.168.1.221(S0504027),因為暫在測試階段,所以網關裝在我用的機器,網關可以裝在MSQLDB上

1.通過ODBC通用方式聯接

代碼:

// A. 安裝HS部件

//?????默認情況下HS部件是安裝的,查詢視圖 SYS.HS_BASE_CAPS 可得出有沒有安裝此部件!

// B. 配置ODBC

//?????在"系統DNS"中配置"ODBC FOR SQLSERVER",例如:[ERPSQL]

// C. 配置TNSNAMES.ORA,路徑:ORACLE_HOME\NETWORK\ADMIN,這一步應該在ORACLEDB(192.168.1.52)上配置!

Lnk2sql =??????????????????# tnsName

??(DESCRIPTION = 

????(ADDRESS_LIST = 

????????(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))?????# 網關IP

???? )

????(CONNECT_DATA = 

????????(SID = hs4sql)????#SID,要和監聽器裡的SID一致!

????) 

????(HS=OK)

// D. 配置listener.ora,路徑:ORACLE_HOME\NETWORK\ADMIN

LISTENER =

??(DESCRIPTION_LIST =

????(DESCRIPTION =

??????(ADDRESS_LIST =

????????(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

??????)

??????(ADDRESS_LIST =

????????(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))

??????)

????)

??)


SID_LIST_LISTENER =

??(SID_LIST =

????(SID_DESC =?????????# 這一段為加入的

????????(SID_NAME = hs4sql)

????????(ORACLE_HOME = D:oracleora9i)

????????(PROGRAM = hsodbc)???# 要使用的HS服務程序.

????) 

??)

// E. 重啟監聽器服務

// F. 編輯ORACLE_HOME\HS\ADMIN內init.ora，這裡是iniths4sql.ora,因為上面的SID=hs4sql

HS_FDS_CONNECT_INFO = ERPSQL?????# B中設置的ODBC名稱

HS_FDS_TRACE_LEVEL = 0

// G. 創建DB LINK,以及測試

SQL>??create database link ora2sql connect to "sa" identified by "pass" using 'Lnk2sql';


Database link created


SQL> select ta001,ta002 from acpta@ora2sql where rownum<5;


TA001 TA002

----- -----------

S710??20020306001

S710??20020315001

S710??20020325001

S710??20020326001

------------

2.通過"透明網關"方式聯接

代碼:

// A. 安裝透明網關,在安裝時選擇自定義安裝,安裝TRANSPARENT GATEWAY FOR SQLSERVER 組件,安裝成功後會產生oracle_homeora90\tg4msql目錄!

// B. 配置TNSNAMES.ORA,路徑:ORACLE_HOME\NETWORK\ADMIN,這一步應該在ORACLEDB(192.168.1.52)上配置!

TG4MSQL =???????????# tnsName

????(DESCRIPTION =

??????(ADDRESS_LIST =

???????? (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))?????# 網關IP

??????)

??????(CONNECT_DATA =

????????(SID = tg4msql )??#SID,要和監聽器裡的SID一致!

??????)

????(HS=OK) 

????)

// C. 配置listener.ora,路徑:ORACLE_HOME\NETWORK\ADMIN

LISTENER =

??(DESCRIPTION_LIST =

????(DESCRIPTION =

??????(ADDRESS_LIST =

????????(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

??????)

??????(ADDRESS_LIST =

????????(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))

??????)

????)

??)


SID_LIST_LISTENER =

??(SID_LIST =

????(SID_DESC =

??????(GLOBAL_DBNAME = tg4msql)

??????(SID_NAME = tg4msql)

??????(ORACLE_HOME = D:oracleora9i)

??????(PROGRAM= tg4msql)

????)

??)

// D. 重啟監聽器服務

// E. 編輯ORACLE_HOME\TG4MSQL\ADMIN內init.ora，這裡是inittg4msql.ora,因為上面的SID=tg4msql

#HS_FDS_CONNECT_INFO="SERVER=ERPSQL;DATABASE=EK",好多人說用這行可以,我用這行的時候出現了不能打開鏈接的錯誤,改下面一行就沒問題了!

HS_FDS_CONNECT_INFO=ERPSQL.EK

HS_FDS_TRACE_LEVEL=OFF

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER

// F. 創建DB LINK,以及測試

SQL>??create database link msql2 connect to "sa" identified by "pass" using 'TG4MSQL';


Database link created


SQL> select ta001,ta002 from acpta@msql2 where rownum<5;


TA001 TA002

----- -----------

S710??20020306001

S710??20020315001

S710??20020325001

S710??20020326001

--------

代碼:

-- 不知什么原因,感覺"通用方式"比"透明網關速度快一點"

SQL> set timing on

SQL> select ta001,ta002 from acpta@ora2sql where rownum<10;


TA001 TA002

----- -----------

S710??20020306001

S710??20020315001

S710??20020325001

S710??20020326001

S710??20020328001

S710??20020329001

S710??20020419001

S710??20020422001

S710??20020425001


9 rows selected


Executed in 0.047 seconds


SQL> select ta001,ta002 from acpta@msql2 where rownum<10;


TA001 TA002

----- -----------

S710??20020306001

S710??20020315001

S710??20020325001

S710??20020326001

S710??20020328001

S710??20020329001

S710??20020419001

S710??20020422001

S710??20020425001


9 rows selected


Executed in 52.281 seconds

--------

3.SQLSERVER訪問ORACLE
環境:windowsxp + sqlserver2000 + Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production IP:192.168.1.221

代碼:

// A. 添加ODBC,OdbcName=DB,OracleSid=DB

// B. 執行

sp_addlinkedserver 'LIORA', 'Oracle', 'MSDAORA', 'DB'

GO

EXEC sp_addlinkedsrvlogin??@rmtsrvname='LIORA',@useself='false',@locallogin='sa',@rmtuser='SYSTEM',@rmtpassword='MANAGER'


select top 10 topic,info from LIORA..SYSTEM.HELP

topic??????????????????????????????????????????????info?????????????????????????????????????????????????????????????????????????????

-------------------------------------------------- -------------------------------------------------------------------------------- 

@??????????????????????????????????????????????????NULL

@???????????????????????????????????????????????????@ ("at" sign)

@???????????????????????????????????????????????????-------------

@??????????????????????????????????????????????????NULL

@???????????????????????????????????????????????????Runs the SQL*Plus statements in the specified command file. The command

@???????????????????????????????????????????????????file can be called from the local file system or from a web server.

@??????????????????????????????????????????????????NULL

@???????????????????????????????????????????????????@ {uri|file_name[.ext]} [arg...]

@??????????????????????????????????????????????????NULL

@???????????????????????????????????????????????????where uri supports HTTP, FTP and gopher protocols in the form:


(影響 10 個資料列)

原文引用 ：
你可以使用這個鏈接引用該篇文章 http://publishblog.blogchina.com/blog/tb.b?diaryID=5059542