Oracle 中的字符集不匹配错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13354436/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Character Set Mismatch Error in Oracle
提问by Soham Shah
I have following query with me that I am trying to run:
我正在尝试运行以下查询：
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
But it gives me ORA-12704: character set mismatch error.
但它给了我 ORA-12704: character set mismatch error.
When I run both the select statement individually, it gives me proper output but when I am doing union of both select, it gives tme character set mismatch error.
当我单独运行两个 select 语句时，它给了我正确的输出，但是当我执行两个 select 的联合时，它给出了 tme 字符集不匹配错误。
What can be wrong here?
这里有什么问题？
回答by DazzaL
as you've confirmed some things are NVARchar'd ..cast the nvarchar to char eg
正如你已经确认有些东西是 NVARchar'd ..cast the nvarchar to 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
fails as "A" is NVARCHAR. so to_char it:
失败，因为“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
or cast the string literal 'hi' to a Nvarchar
或将字符串文字 'hi' 转换为 Nvarchar
SQL> select 1, n'hi' from dual
2 union all
3 select 2, a from tab;
1 N'
---------- --
1 hi
2 a
回答by mostafa.S
SELECT Script from (
SELECT 9 as ColOrder, ' INSERT INTO PROJ VALUES(' || to_char(ID) || ',''' || to_char(Name) || ''', ''' || to_char(Version) || ''', ''ABCD'', sysdate , ''ABCD'', sysdate);' as Script FROM PROJ where Name like '%[Param.1]%'
union
SELECT 11 as ColOrder,' INSERT INTO PROJMOD VALUES(' || to_char(ID) || ', ' || to_char(ProjID) || ', ' || to_char(ModID) || ', ' || to_char(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
I just added TO_CHAR functions, and that will work. apparently you have fields with character data types that cannot be implicitly cast by Oracle, so you just need to cast it explicitly yourself, however be careful to avoid any character loss due to cast.
我刚刚添加了 TO_CHAR 函数，这将起作用。显然，您拥有无法由 Oracle 隐式转换的字符数据类型的字段，因此您只需要自己显式转换它，但要小心避免由于转换导致的任何字符丢失。
回答by egemen
If you take this error, you should look at two conditions.
如果你接受这个错误，你应该看看两个条件。
- All column name or alias must be same for all tables
- All column must be same type TableA(col NVARCHAR2), TableB(col NVARCHAR2)
- 所有表的所有列名或别名必须相同
- 所有列必须是相同类型的 TableA(col NVARCHAR2), TableB(col NVARCHAR2)
