oracle - Oracle 中的字符集不匹配错误

Question

我正在尝试运行以下查询:

SELECT Script from (

SELECT 9 as ColOrder, ' INSERT INTO PROJ VALUES(' || ID || ',''' || Name || ''', ''' || Version || ''', ''ABCD'', sysdate , ''ABCD'', sysdate);' as Script FROM PROJ where Name like '%[Param.1]%'

union

SELECT 11 as ColOrder,' INSERT INTO PROJMOD VALUES(' || ID || ', ' || ProjID || ', ' || ModID || ', ' || ObjID || ', ''ABCD'', sysdate, ''ABCD'', sysdate);' as Script FROM PROJMOD where ProjID in ( select ID from PROJ where Name like '%[Param.1]%')

) x

Order by ColOrder

但它给了我ORA-12704:字符集不匹配错误。

当我单独运行两个 select 语句时，它会给出正确的输出，但是当我对两个 select 进行并集时，它会给出 tme 字符集不匹配错误。

这里可能出了什么问题？

Answer 1

正如您所确认的，有些内容是 NVARchar'd ..将 nvarchar 转换为 char 例如

SQL> create table tab(a nvarchar2(2));

Table created.

SQL> insert into tab values ('a');

1 row created.

SQL> select 1, 'hi' from dual
  2  union all
  3  select 2, a from tab;
select 1, 'hi' from dual
          *
ERROR at line 1:
ORA-12704: character set mismatch

失败，因为“A”是 NVARCHAR。所以 to_char 它:

SQL> select 1, 'hi' from dual
  2  union all
  3  select 2, to_char(a) from tab;

         1 'HI'
---------- ----
         1 hi
         2 a

或将字符串文字“hi”转换为 Nvarchar

   SQL> select 1, n'hi' from dual
      2  union all
      3  select 2, a from tab;

             1 N'
    ---------- --
             1 hi
             2 a