ORA-12704: character set mismatch字符集不匹配

在做两个select语句的union all的过程中，爆出了这个问题，这两个语句单独执行都能顺利执行

于是可能问题在系统面对A语句时生成的  int，char字段，B语句生成的int，varchar2字段

那么问题就简单了，将其转化成相同数据类型的数据即可。

create table t_test1(id number, name varchar2(100));
create table t_test2(id number ,name nvarchar2(100));
--------------------
select t.* from T_TEST1 t;
select t.* from T_TEST2 t;
--------------------
insert into t_test1 values(1,'nameFromTest1');
insert into t_test2 values(2,'nameFromTest2');
--------------------union all查询
select id, name from t_test1
union all
select id, name from t_test2;

然可能工作中比较复杂，比如name字段是由若干内容拼成的，这样系统就会自动判断这个字段该属于什么类型

 

解决办法如下

---------------------解决办法1  to_char  to_nchar
select id, name from t_test1
union all
select id, to_char(name) from t_test2;
----------------- to_nchar    cast      translate...using  将varchar2转为nvarchar2
---------------------------------to_nchar
select id, to_nchar(name) from t_test1
union all
select id, name from t_test2;
----------------------------------cast
select id, cast(name as nvarchar2(100)) from t_test1
union all
select id, name from t_test2;

----------------------------------translate...using  2   nchar_cs
select id, translate(name using nchar_cs) from t_test1
union all
select id, name from t_test2;
----------------------------------translate...using  2 char_cs
select id, name  from t_test1
union all
select id, translate(name using char_cs) from t_test2;