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

1、1、新建链接服务器的方法在图1中选中“链接服务器”，右键选择“新建链接服务器”，如图2，配置相关参数。2、配置相关参数在“常规”选项中，填写“链接服务器名称”，访问接口选中“ Microsoft OLE DB Provider for Oracle”项。其中产品名称、数据源、访问接口字符串请填写配置的Oracle客户端在本地配置的net服务名。如图3所示。在“安全性”项中，选中使用此安全上下文建立连接，填写远程登录用户和密码，即登录远程Oracle服务器的用户和密码。如图4所示。3、异构数据库访问语句格式比如我们建立了链接服务器ORADBCONN，而Oracle中在HRSOFT用户下面建立了表

2、WEBUSER，那么我们的SQL语句就是：-清空Oracle表中的数据DELETE FROM ORADBCONN.HRSOFT.WEBUSER-将SQLServer中的数据写到Oracle中INSERT into ORADBCONN. HRSOFT. WEBUSERSELECT * FROM WEBUSER如果报告成功，那么我们的数据就已经写入到Oracle中了。用SELECT * FROM ORADBCONN.HRSOFT.WEBUSER查看Oracle数据库中是否已经有数据了。4、链接服务器应用A、查询Oracle数据表方式一(这种方式，当Oracle与SQLServer的数据类型不一致时

3、经常报错,且速度稍慢)：select * from LINK2ORACLE.ORACLE_USER_NAME.TABLE_NAME;我在执行该语句经常报类似错误信息：链接服务器 LINK2ORACLE 的 OLE DB 访问接口 MSDAORA 为列提供的元数据不一致。对象 CMCC.OS2_GIS_CELL 的列 ISOPENED (编译时序号为 20)在编译时有 130 的 DBTYPE，但在运行时有 5。B、查询Oracle数据表方式二(经试验，这种方式使用起来很顺畅，不报错，且速度几乎和在Oralce中一样快)：select * from openquery(LINK2ORACLE,s

4、elect * 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_g

5、is_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=

6、(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_DAT

7、A =(SID= TJTEST)新建作業里的代碼：delete from XBLD_InformationINSERT 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_

8、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) BMfrom(select mjl.job_name, mjl.num_pcbs, mjl.num_arr

9、ays, 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

10、) 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

11、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_na

12、me,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() ov

13、er( 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_coun

14、t),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( partit

15、ion 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( s

16、elect 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=Prep

17、reg)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(+