前几天由於工作的原因查找了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上!
http://www.itpub.net/535786.html
[@more@]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服務程序. ) ) 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 TG4MSQL = # tnsName (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521)) # 网關IP ) (CONNECT_DATA = (SID = tg4msql ) #SID,要和監听器裡的SID一致! ) (HS=OK) ) 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) ) ) #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 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 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/8799875/viewspace-905752/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8799875/viewspace-905752/