oracle和sqlserver互訪(轉)
oracle和sqlserver互訪!
2.通過"透明閘道器"方式聯接
3.SQLSERVER訪問ORACLE
環境:windowsxp + sqlserver2000 + Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production IP:192.168.1.221
前幾天由於工作的原因查詢了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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle和sqlserver互訪(1)OracleSQLServer
- oracle和sqlserver互訪(2)OracleSQLServer
- oracle和sqlserver互訪(3)OracleSQLServer
- sqlserver 行列互轉實現小結SQLServer
- Oracle,SqlServer,Access資料庫通用訪問類設計(轉)OracleSQLServer資料庫
- Oracle透明閘道器訪問SQLServer資料庫OracleSQLServer資料庫
- Windows XP 解決互訪問題(轉)Windows
- SQLServer和Oracle常用函式對比SQLServerOracle函式
- 《轉》SqlServer和Oracle中一些常用的sql語句3-行列轉換SQLServerOracle
- 外網訪問本地sqlserverSQLServer
- String 和Inputstreem互轉
- Oracle和sqlserver資料型別對應OracleSQLServer資料型別
- SQLServer和Oracle的常用函式對比SQLServerOracle函式
- 從SqlServer轉手Oracle的一些坑SQLServerOracle
- [轉]SQLServer跨伺服器訪問資料庫(openrowset/opendatasource/openquery)SQLServer伺服器資料庫ENQ
- Oracle VirtualBox 配置宿主機與虛擬機器互訪Oracle虛擬機
- sqlserver連線oracleSQLServerOracle
- SQLSERVER 連線 ORACLESQLServerOracle
- oracle DBLink sqlserverOracleSQLServer
- ORACLE臨時表和SQLSERVER臨時表異同OracleSQLServer
- Oracle表結構轉換SqlSERVER表結構 指令碼OracleSQLServer指令碼
- Python3 dict和str互轉Python
- [轉]decode函式和行列互換函式
- SQLServer解析xml到OracleSQLServerXMLOracle
- GoldenGate實現oracle和sqlserver雙向資料同步GoOracleSQLServer
- (轉)SqlServer裡DateTime轉字串SQLServer字串
- SqlServer效能檢測和優化工具使用詳細(轉)SQLServer優化
- 大資料視覺化安全保障之私密訪問:讓訪問和互動更安全大資料視覺化
- 10g透明閘道器訪問sqlserverSQLServer
- 二維陣列和稀疏陣列互轉陣列
- JS於string 和 json互轉物件JSON物件
- (轉)隨機選擇行的SQL語句? ORACLE SQLSERVER ECT.隨機SQLOracleServer
- 透明閘道器 oracle 10g for sqlserver 和sybase的配置Oracle 10gSQLServer
- 如何限定IP訪問Oracle資料庫-轉Oracle資料庫
- (原)將Oracle遷移到SQLServerOracleSQLServer
- WEKA連線MySQL,Oracle,SQLServerMySqlOracleServer
- SQLServer訪問Oracle(通過同義詞-檢視-資料字典)出現的問題SQLServerOracle
- 【meil 】SQLServer Transact SQL全集(轉)SQLServer