Character set mismatch error in Oracle

Question

I have the following request 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 that gives me ORA-12704: character set mismatch error.

When I run both select statements separately, it gives me the correct output, but when I combine both selects, it gives the tme character set mismatch error.

What could be wrong here?

Answer 1

as you have confirmed that some things nvarchar'd..cast nvarchar for char for example

 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 

does not work because "A" is NVARCHAR. therefore 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 translate the string literal 'hi' to Nvarchar

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

Answer 2

 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 the TO_CHAR function and it will work. apparently you have fields with characteristic data types that cannot be implicitly applied by Oracle, so you just need to explicitly specify it yourself, however, be careful to avoid character loss due to casting.

Answer 3

If you take this error, you should look at two conditions.

• All column names or aliases must be the same for all tables
• All columns must be of the same type: TableA (col NVARCHAR2), TableB (col NVARCHAR2)