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，但是未得到官方或者权威的确认)
