天萃荷净
开发DBA反映Oracle数据库两库之间通过dblink相连。在使用DML语句insert插入数据时操作的数据类型发生了改变
1.Oracle insert列属性
[oracle@saas-xunzhi-db1 ~]$ sqlplus testga/testga
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 12 16:26:56
Copyright (c) 1982, , 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_oracleplus;
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.通过Oralce 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.使用Oracle dblink插入数据并查询
SQL> INSERT INTO t_oracleplus
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_oracleplus;
PROCESS_INSTANCE_ID STATUS_NEW PACKAGE_NA
------------------- ---------- ----------
4755 1 呈批处理
4836 1 公文处理
这里出现异常，怀疑case中传入的值，都走到else那边了，怀疑是在使用dblink 的时候，char的类型发生的改变，转为了number，导致case匹配失败，所以走到else。
4.在Oracle dblink端创建视图，插入数据
SQL> CREATE VIEW GZZJ.v_oracleplus
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_oracleplus
2 select * from v_oracleplus@oldmoa;
2 rows created.
SQL> commit;
Commit complete.
SQL> select * from t_oracleplus;
PROCESS_INSTANCE_ID STATUS_NEW PACKAGE_NA
------------------- ---------- ----------
4755 3 呈批处理
4836 3 公文处理
通过远程视图，接触数据类型发生改变异常(怀疑是dblink 在insert时候，char数据类型变成了number，但是未得到官方或者权威的确认)
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【案例】Oracle dblink 数据库连接dblink insert操作数据类型发生改变
