oracle和sqlserver互訪(轉)

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_HOMENETWORKADMIN,這一步應該在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_HOMENETWORKADMIN
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_HOMEHSADMIN內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_homeora90tg4msql目錄!

// B. 配置TNSNAMES.ORA,路徑:ORACLE_HOMENETWORKADMIN,這一步應該在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_HOMENETWORKADMIN
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_HOMETG4MSQLADMIN內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 個資料列)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242228/，如需轉載，請註明出處，否則將追究法律責任。