通过创建SQLServer 2005到 Oracle10g 的链接服务器实现异构数据库数据转换方案

新建链接服务器

在图1中选择链接服务器

在常规选项中填写链接服务器名称

“ Microsoft OLE DB Provider for Oracle项目。请填写产品名称、数据源和访问接口字符串Oracle本地配置客户端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;

我经常报告类似的错误信息，链接服务器 "LINK2ORACLE" 的 OLE DB 访问接口 "MSDAORA" 为列提供的元数据不一致。对象 ""CMCC"."OS2_GIS_CELL"" 的列 "ISOPENED" (编译时序号为 编译时有20) 130 的 "DBTYPE"，但运行时有 5。

B、查询Oracle数据表模式2(经试验，使用顺畅，不报错，速度几乎和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、更方便的方法：创建同义词：

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，状态 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)))

新建作业里的代码：

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 y 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