Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Character Set Mismatch Error in Oracle

Tags:

oracle

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.

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.

What can be wrong here?

like image 735
Soham Shah Avatar asked Nov 13 '12 01:11

Soham Shah


2 Answers

as you've confirmed some things are NVARchar'd ..cast the nvarchar to char eg

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:

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

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

             1 N'
    ---------- --
             1 hi
             2 a
like image 146
DazzaL Avatar answered Sep 18 '22 15:09

DazzaL


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.

like image 43
mostafa.S Avatar answered Sep 17 '22 15:09

mostafa.S