联系:手机/微信(+86 17813235971) QQ(107644445)
标题:诡异dblink问题解决–dblink insert操作数据类型发生改变
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
1.展示列属性
[oracle@saas-xunzhi-db1 ~]$ sqlplus testga/testga SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 12 16:26:56 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options SQL> desc t_xifenfei; Name Null? Type ----------------------------------------- -------- ---------------------------- PROCESS_INSTANCE_ID NOT NULL NUMBER(10) STATUS_NEW NUMBER PACKAGE_NAME VARCHAR2(50) SQL> desc wf_proc_inst@oldmoa Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(10) NAME VARCHAR2(500) PROC_ID NOT NULL NUMBER(10) PARE_INST_ID NOT NULL NUMBER(10) PARE_ACTI_ID NOT NULL NUMBER(10) INST_CREATOR VARCHAR2(40) CREAT_DATE DATE STATUS CHAR(1) ATT1 NUMBER(10) ATT2 VARCHAR2(255) SEQVALUE VARCHAR2(50)
从这里可以看出,原表的status列是char,而目标表的STATUS_NEW列是number类型
2.通过dblink查询列值
SQL> select t.inst_id as ID, 2 case w.status 3 when '0' then 4 4 5 when '2' then 6 3 7 else 8 1 9 end as status_new, 10 p.name as PACKAGE_NAME 11 from wf_proc_inst@oldmoa i 12 left join wf_proc_info_inst@oldmoa t on t.inst_id = i.id 13 left join wf_proc_type@oldmoa p on t.type_id = p.id 14 left join wf_proc_inst@oldmoa w on t.inst_id = w.id 15 where t.inst_id = i.id 16 and t.proc_id <> 53 17 and t.is_ok = 1 18 AND t.inst_id <= 4837 19 AND t.inst_id>=4735; ID STATUS_NEW PACKAGE_NAME ---------- ---------- -------------------------- 4755 3 呈批处理 4836 3 公文处理
从这里可以看出来,case工作正常,传入的char类型,通过case转换为number类型
3.使用dblink插入数据并查询
SQL> INSERT INTO t_xifenfei 2 select t.inst_id as ID, 3 case w.status 4 when '0' then 5 4 6 when '2' then 7 3 8 else 9 1 10 end as status_new, 11 p.name as PACKAGE_NAME 12 from wf_proc_inst@oldmoa i 13 left join wf_proc_info_inst@oldmoa t on t.inst_id = i.id 14 left join wf_proc_type@oldmoa p on t.type_id = p.id 15 left join wf_proc_inst@oldmoa w on t.inst_id = w.id 16 where t.inst_id = i.id 17 and t.proc_id <> 53 18 and t.is_ok = 1 19 AND t.inst_id <= 4837 20 AND t.inst_id>=4735; 2 rows created. SQL> commit; Commit complete. SQL> col package_name for a10 SQL> select * from t_xifenfei; PROCESS_INSTANCE_ID STATUS_NEW PACKAGE_NA ------------------- ---------- ---------- 4755 1 呈批处理 4836 1 公文处理
这里出现异常,怀疑case中传入的值,都走到else那边了,怀疑是在使用dblink 的时候,char的类型发生的改变,转为了number,导致case匹配失败,所以走到else。
4.在dblink端创建视图,插入数据
SQL> CREATE VIEW GZZJ.v_xifenfei 2 AS 3 select t.inst_id as ID, 4 case to_char(w.status) 5 when '0' then 6 4 7 when '2' then 8 3 9 else 10 1 11 end as status_new, 12 p.name as PACKAGE_NAME 13 from GZZJ.wf_proc_inst i 14 left join GZZJ.wf_proc_info_inst t on t.inst_id = i.id 15 left join GZZJ.wf_proc_type p on t.type_id = p.id 16 left join GZZJ.wf_proc_inst w on t.inst_id = w.id 17 where t.inst_id = i.id 18 and t.proc_id <> 53 19 and t.is_ok = 1 20 AND t.inst_id <= 4837 21 AND t.inst_id>=4735; View created. SQL> insert into t_xifenfei 2 select * from v_xifenfei@oldmoa; 2 rows created. SQL> commit; Commit complete. SQL> select * from t_xifenfei; PROCESS_INSTANCE_ID STATUS_NEW PACKAGE_NA ------------------- ---------- ---------- 4755 3 呈批处理 4836 3 公文处理
通过远程视图,接触数据类型发生改变异常(怀疑是dblink 在insert时候,char数据类型变成了number,但是未得到官方或者权威的确认)
直接cast也正常