很多時(shí)間，要在異構(gòu)數(shù)據(jù)庫(kù)之間進(jìn)行數(shù)據(jù)遷移或抽取，如果在SQL中提取ORACLE的數(shù)據(jù)，可以通過(guò)ODBC、OLEDB等多種方式，要從ORACLE提取SQL中的數(shù)據(jù)，大都是通過(guò)透明網(wǎng)關(guān)來(lái)實(shí)現(xiàn)的。
在異構(gòu)數(shù)據(jù)抽取過(guò)程中，最好采用SQL92標(biāo)準(zhǔn)的語(yǔ)法編寫SQL代碼，同時(shí)要注意不同數(shù)據(jù)庫(kù)之間數(shù)據(jù)類型的轉(zhuǎn)換關(guān)系，如ORACLE的日期類型用DATE、SQL用Datetime等。
一 關(guān)于ORACLE的透明網(wǎng)關(guān)配置
ORACLE安裝時(shí)（9i），將TRANSPARENT GATEWAY FOR MSSQL選中，在ORACLE主目錄\BIN中，有個(gè)tg4msql.exe程序，它是透明網(wǎng)關(guān)程序啦，同時(shí)在主目錄中還有tgrmsql的一個(gè)目錄， ORACLE_HOME\tg4msql\admin目錄中的inittg4msql.ora就是需要進(jìn)行配置才能在ORACLE中連接SQL。
1 如何配置透明網(wǎng)關(guān)呢？打開inittg4msql.ora:
#
# HS init parameters
# xzh代表SQL服務(wù)名，pubs代表要訪問(wèn)的SQL數(shù)據(jù)庫(kù)
#
HS_FDS_CONNECT_INFO="SERVER=xzh;DATABASE=pubs"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
2 監(jiān)聽的配置oracle_home\network\admin\Listiner.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xzh)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xzh)(PORT = 1527))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = xzh.world)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = xzh)
)
(SID_DESC =
(GLOBAL_DBNAME = tg4msql)
(PROGRAM = tg4msql)
(SID_NAME = tg4msql)
(ORACLE_HOME = D:\oracle\ora92)
)
)
加亮代碼是要在監(jiān)聽文件中新加部分，GLOBAL_DBNAME、SID_NAME可以任意，PROGRM必須指向tg4msql如圖2.1所示。
3 本地服務(wù)文件的配置oracle_home\network\admin\TnsNames.ora
XZH =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xzh)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xzh.world)
)
)
TG4MSQL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xzh)(PORT = 1527))
)
(CONNECT_DATA =
(SID = tg4msql)
)
(HS = OK)
)
使用SQL的本地服務(wù)名為TG4MSQL，可以任意寫，SID必須是Listiner.ora中指定的那個(gè)SID_NAME，PORT也必須是監(jiān)聽中指定的PORT，主機(jī)等都要符合一致。 到此為止，我們把透明網(wǎng)關(guān)大致已經(jīng)配置好啦，如果要訪問(wèn)SQL，還要使用數(shù)據(jù)庫(kù)鏈才方便。
4 在SQL中創(chuàng)建登錄賬號(hào)xzh口令xzh，使用可以訪問(wèn)PUBS數(shù)據(jù)庫(kù)。
CREATE
DATABASE
LINK sql CONNECT TO xzh IDENTIFIED BY xzh
USING ‘TG4MSQL’
這里的USING ‘TG4MSQL’是tnsnames.ora中配置好的本地服務(wù)名，sql是我們以后要引用的數(shù)據(jù)庫(kù)鏈名。
SQL>SELECT * FROM sales@sql
如果有數(shù)據(jù)返回，表示我們?cè)L問(wèn)SQL數(shù)據(jù)庫(kù)的通道已經(jīng)建成，請(qǐng)大膽使用吧，但對(duì)遠(yuǎn)程異構(gòu)數(shù)據(jù)庫(kù)不充許進(jìn)行DDL操作。
SQL>CREATE TABLE all_users@tg4msql FRIN all_users;
*
ERROR 位于第 1 行:
ORA-02021: 不允許對(duì)遠(yuǎn)程數(shù)據(jù)庫(kù)進(jìn)行 DDL 操作
二 SQL訪問(wèn)ORACLE的方法
1 通過(guò)行集函數(shù)OPENDATASOURCE
OPENDATASOURCE ( provider_name, init_string )
SELECT
*
FROM OPENDATASOURCE(
'MSDAORA',
'Data Source=xzh.oracle;User ID=POS;Password=POS')..POS.A0325
MSDAORA是OLEDB FOR ORACLE的驅(qū)動(dòng)，初始化字符串指定本地服務(wù)名、用戶名、口令。然后引用表中數(shù)據(jù)時(shí)要以服務(wù)器..用戶名.表名。注意一定是四部分組成，用戶名與表名一定要大寫。
SELECT * INTO PUBS.DBO.A0325 FROM
OPENDATASOURCE(
'MSDAORA',
'Data Source=xzh.oracle;User ID=POS;Password=POS' )..POS.A0325
將ORACLE中POS模式的A0325導(dǎo)入SQL的PUBS數(shù)據(jù)庫(kù)。
2 通過(guò)SQL的鏈接服務(wù)器引用ORACLE的數(shù)據(jù)
--查看已經(jīng)存在的鏈接服務(wù)器
SELECT＊FROM SYSSERVERS
--添加鏈接服務(wù)器到SQL
EXEC SP_ADDLINKEDSERVER
@server = 'ORCL',
--ORCL是SQL中鏈接服務(wù)器名稱
@srvproduct = 'Oracle',
--Oracle
固定的
@provider = 'MSDAORA',
--MSDAORA
固定的
@datasrc = 'xzh.oracle'
--DataSrc
本地服務(wù)名
EXEC SP_DROPSERVER ‘ORCL’
--刪除鏈接服務(wù)器
在這里聲明一下，如果現(xiàn)在就訪問(wèn)ORACLE肯定不能，因?yàn)闆]有提供登錄ORACLE的用戶名/口令EXEC SP_ADDLINKEDSRVLOGIN 'ORCL', false, 'sa', 'POS', 'POS'Sa是SQL本地登錄賬號(hào)，POS/POS是ORACLE的登錄賬號(hào)，但這句話對(duì)我們要達(dá)到的目的沒有幫助。
SELECT * FROM ORCL..POS.A0325
還是四部分組成，注意事項(xiàng)同上，為什么不行呢，本人一直在尋找解決辦法，最后發(fā)現(xiàn)通過(guò)SQL語(yǔ)句沒法解決，只有打開SQL的企業(yè)管理器。
SELECT * FROM ORCL..POS.A0325
--這次終于OK。
SELECT * FROM OPENQUERY(ORCL, 'SELECT * FROM POS.A0325')
3 使用SQL的OPENROWSET函數(shù)
SELECT a.*
FROM OPENROWSET('MSDAORA',
'xzh.oracle';'POS';'POS', POS.A0325) AS a ORDER BY a.ID
有些地方要用別名才能引用，請(qǐng)大家注意。
?。词褂肙DBC的方式
SELECT A.*
FROM OPENROWSET('MSDASQL','ORCL_ODBC';'POS';'POS',
POS.A0325) AS
A
ORDER BY A.ID
ORCL_ODBC是ORACLE的ODBC數(shù)據(jù)源，創(chuàng)建ODBC不用我說(shuō)吧，以上所有代碼在SQL QUERY ANALYZE 中通過(guò)。
三 總結(jié)
關(guān)于SQL訪問(wèn)ORACLE有四種方式，其中通過(guò)ODBC與鏈接服務(wù)器的方式做起來(lái)難度較大，對(duì)手新手會(huì)麻煩一些，使用OLE DB FOR ORACL驅(qū)動(dòng)時(shí)，有OPENDATASOURCE與OPENROWSET函數(shù)可以使用，都很方便，筆記喜歡使用后者，交待一下，在數(shù)據(jù)存取方面，這四種方面效率相當(dāng)。
本站僅提供存儲(chǔ)服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)
點(diǎn)擊舉報(bào)。
