通過創(chuàng)建SQLServer 2005到 Oracle10g 的鏈接服務器實現異構數據

通過創(chuàng)建SQL Server 2005到 Oracle10g 的鏈接服務器實現異構數據庫數據轉換方案,需要的朋友可以參考下。

1、新建鏈接服務器

在圖1中選中“鏈接服務器”,右鍵選擇“新建鏈接服務器”,如圖2,配置相關參數。

在“常規(guī)”選項中,填寫“鏈接服務器名稱”,訪問接口選中

“ Microsoft OLE DB Provider for Oracle”項。其中產品名稱、數據源、訪問接口字符串請?zhí)顚懪渲玫腛racle客戶端在本地配置的net服務名。如圖3所示。

在“安全性”項中,選中使用此安全上下文建立連接,填寫遠程登錄用戶和密碼,即登錄遠程Oracle服務器的用戶和密碼。如圖4所示。

3、異構數據庫訪問語句格式

比如我們建立了鏈接服務器ORADBCONN,而Oracle中在HRSOFT用戶下面建立了表WEBUSER,那么我們的SQL語句就是:

–清空Oracle表中的數據

DELETE FROM ORADBCONN..HRSOFT.WEBUSER

–將SQLServer中的數據寫到Oracle中

INSERT into ORADBCONN.. HRSOFT. WEBUSER

SELECT * FROM WEBUSER

如果報告成功,那么我們的數據就已經寫入到Oracle中了。

用SELECT *  FROM ORADBCONN..HRSOFT.WEBUSER

查看Oracle數據庫中是否已經有數據了。

4、鏈接服務器應用

A、查詢Oracle數據表方式一(這種方式,當Oracle與SQLServer的數據類型不一致時經常報錯,且速度稍慢):

select * from [LINK2ORACLE]..[ORACLE_USER_NAME].TABLE_NAME;

  我在執(zhí)行該語句經常報類似錯誤信息:鏈接服務器 “LINK2ORACLE” 的 OLE DB 訪問接口 “MSDAORA” 為列提供的元數據不一致。對象 “”CMCC”.”OS2_GIS_CELL”” 的列 “ISOPENED” (編譯時序號為 20)在編譯時有 130 的 “DBTYPE”,但在運行時有 5。

  B、查詢Oracle數據表方式二(經試驗,這種方式使用起來很順暢,不報錯,且速度幾乎和在Oralce中一樣快):

  select * from openquery(LINK2ORACLE,’select * from OracleUserName.TableName’)

  您可以把openquery()當成表來使用。

  C、舉個例子(將Oralce用戶HRSOFT下的用戶表WEBUSER導入到SQLServer2005數據庫中):

  select * into OS_GIS_WEBUSER from openquery(LINK2ORACLE,’select * from HRSOFT. WEBUSER)

  D、更便捷的方式:通過創(chuàng)建同義詞進行便捷查詢:

  CREATE SYNONYM OS_GIS_CELL FOR [ORACLELK]..[CMCC].OS_GIS_CELL;

  select * from os_gis_cell;

  select * from os_gis_cell a where a.CellName is null;

注意:涉及 Oracle 部分的 SQL 語句,尤其是 [ORACLELINK]..[ORACLE_USER_NAME].TABLE_NAME 一定要大寫,否則會報類似錯誤:

消息 7314,級別 16,狀態(tài) 1,第 1 行

鏈接服務器 “ORACLELK” 的 OLE DB 訪問接口 “MSDAORA” 不包含表 “”CMCC”.”OS2_gis_CELL””。該表不存在,或者當前用戶沒有訪問該表的權限。

配置數據源:

工程INPLAN:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.6)(PORT=1521)))(CONNECT_DATA=(SID=sa)(SERVER=DEDICATED)))

ERP:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.3.25)(PORT = 1528)))(CONNECT_DATA =(SID= TJTEST)))

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.3.25)(PORT = 1528)))(CONNECT_DATA =(SID= TJTEST)))

新建作業(yè)里的代碼:

delete from XBLD_Information

INSERT INTO XBLD_Information(job_name, num_layers, JB_PP1, JB_PC1, JB_PP2, JB_PC2, JB_PP3, JB_PC3, PP_PP1, PP_PC1, PP_PP2, PP_PC2, PP_PP3, PP_PC3, PP_PP4,PP_PC4,BM)

select * from openquery(INPLAN,’

select mjs15.job_name, mjs15.num_layers,mjs1.MM1 JB_PP1,mjs1.MC1 JB_PC1,mjs2.MM2 JB_PP2,mjs2.MC2 JB_PC2,mjs3.MM3 JB_PP3,mjs3.MC3 JB_PC3,mjs11.MM1 PP_PP1,mjs11.MC1 PP_PC1,mjs12.MM2 PP_PP2,mjs12.MC2 PP_PC2,mjs13.MM3 PP_PP3,mjs13.MC3 PP_PC3,mjs14.MM4 PP_PP4,mjs14.MC4 PP_PC4,Substr(mjs15.job_name,5,2) BM

from

(select mjl.job_name,

mjl.num_pcbs,

mjl.num_arrays,

mjl.num_panles_,

mjl.num_layers,

substr(mjl.op_size_y_string_,instr(mjl.op_size_y_string_,”=”)+1,length(mjl.op_size_y_string_)-instr(mjl.op_size_y_string_,”=”)+1) y_size, –祇へ糴

substr(mjl.op_size_x_string_,instr(mjl.op_size_x_string_,”=”)+1,length(mjl.op_size_x_string_)-instr(mjl.op_size_x_string_,”=”)+1) x_size –祇へ

from tj.my_job_list mjl

) mjs15,

(select *

from

(select mjs.job_name,

decode(DENSE_RANK() over( partition by job_name order by material_name,material_count),1,material_name) MM1,

decode(DENSE_RANK() over( partition by job_name order by material_name,material_count),1,material_count) MC1

from tj.my_job_stackup_material_count mjs

where mjs.type_T=”Core”

)where MM1 is not null

) mjs1,

(select *

from

(select mjs.job_name,

decode(DENSE_RANK() over( partition by job_name order by material_name,material_count),2,material_name) MM2,

decode(DENSE_RANK() over( partition by job_name order by material_name,material_count),2,material_count)MC2

from tj.my_job_stackup_material_count mjs

where mjs.type_T=”Core”

) where MM2 is not null

) mjs2,

(select *

from

(select mjs.job_name,

decode(DENSE_RANK() over( partition by job_name order by material_name,material_count),3,material_name) MM3,

decode(DENSE_RANK() over( partition by job_name order by material_name,material_count),3,material_count)MC3

from tj.my_job_stackup_material_count mjs

where mjs.type_T=”Core”

) where MM3 is not null

) mjs3,

(select *

from

( select mjs.job_name,

decode(DENSE_RANK() over( partition by job_name order by material_name,material_count),1,material_name) MM1,

decode(DENSE_RANK() over( partition by job_name order by material_name,material_count),1,material_count) MC1

from tj.my_job_stackup_material_count mjs

where mjs.type_T=”Prepreg”

)where MM1 is not null

) mjs11,

(select *

from (

select mjs.job_name,

decode(DENSE_RANK() over( partition by job_name order by material_name,material_count),2,material_name) MM2,

decode(DENSE_RANK() over( partition by job_name order by material_name,material_count),2,material_count)MC2

from tj.my_job_stackup_material_count mjs

where mjs.type_T=”Prepreg”

)where MM2 is not null

) mjs12,

(select *

from

( select mjs.job_name,

decode(DENSE_RANK() over( partition by job_name order by material_name,material_count),3,material_name) MM3,

decode(DENSE_RANK() over( partition by job_name order by material_name,material_count),3,material_count)MC3

from tj.my_job_stackup_material_count mjs

where mjs.type_T=”Prepreg”

)

where MM3 is not null

) mjs13,

(select distinct *

from

( select mjs.job_name,

decode(DENSE_RANK() over( partition by job_name order by material_name,material_count),4,material_name) MM4,

decode(DENSE_RANK() over( partition by job_name order by material_name,material_count),4,material_count)MC4

from tj.my_job_stackup_material_count mjs

where mjs.type_T=”Prepreg”

) where MM4 is not null

) mjs14

where mjs15.job_name=mjs1.job_name(+)

and mjs15.job_name=mjs2.job_name(+)

and mjs15.job_name=mjs3.job_name(+)

and mjs15.job_name=mjs11.job_name(+)

and mjs15.job_name=mjs12.job_name(+)

and mjs15.job_name=mjs13.job_name(+)

and mjs15.job_name=mjs14.job_name(+)

‘)

UPDATE XBLD_Information SET BM=b.Describe from XBLD_Information a, XBLD_BM b where a.bm=b.id

? 版權聲明
THE END
喜歡就支持一下吧
點贊12 分享