Home » SQL & PL/SQL » SQL & PL/SQL » ORA-12704: Character set mismatch (Oracle 10g Enterprise Edition Release 10.2.0.4.0)
ORA-12704: Character set mismatch [message #429453] Tue, 03 November 2009 22:49 Go to next message
JoeyD
Messages: 7
Registered: August 2009
Junior Member
Hi, I'm currently testing an implementation of Oracle 10g Enterprise Release 10.2.0.4.0 - 64bit (an upgrade from 9i) and I'm experiencing the ora-12704 error when executing the following SQL (which worked fine under 9i):

SELECT account_id
FROM stg_acct_master
MINUS
SELECT TRIM(gmaid)
FROM f0901

Background information:
- The field account_id from table stg_acct_master is a varchar2( 16 ).
- The field gmaid from view f0901 is an nchar( 8 ).

- Table stg_acct_master is an oracle 10g table.
- The view f0901 points to a SQL Server table.

- The following parameters are set as:
NLS_CHARACTERSET = WE8ISO8859P1
NLS_NCHAR_CHARACTERSET = AL16UTF16
(Our setting under 9i for NLS_CHARACTERSET is US7ASCII)

- If I execute: SELECT to_char(gmaid) FROM f0901;
I get ¿¿¿¿ as the result for each row, instead of 99000221 as an example value.

- Executing: SELECT DUMP(gmaid,1017) FROM f0901;
results in: Typ=96 Len=8 CharacterSet=AL16UTF16: 9,9,0,0,0,2,2,1

I'm not sure how to resolve this issue - any help appreciated!

Pls let me know if you need any other background info.
thanks
Re: ORA-12704: Character set mismatch [message #429455 is a reply to message #429453] Tue, 03 November 2009 22:56 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
12704, 00000, "character set mismatch"
// *Cause: One of the following:
//         - The string operands(other than an nlsparams argument) to an
//           operator or built-in function do not have the same character
//           set. 
//         - An nlsparams operand is not in the database character set.
//         - String data with character set other than the database character
//           set is passed to a built-in function not expecting it.
//         - The second argument to CHR() or CSCONVERT() is not CHAR_CS or
//           NCHAR_CS.
//         - A string expression in the VALUES clause of an INSERT statement,
//           or the SET clause of an UPDATE statement, does not have the
//           same character set as the column into which the value would
//           be inserted.
//         - A value provided in a DEFAULT clause when creating a table does
//           not have the same character set as declared for the column.
//         - An argument to a PL/SQL function does not conform to the
//           character set requirements of the corresponding parameter.
// *Action:


What is returned from following:

SELECT ASCIISTR(gmaid) FROM f0901;
Re: ORA-12704: Character set mismatch [message #429456 is a reply to message #429453] Tue, 03 November 2009 23:04 Go to previous messageGo to next message
JoeyD
Messages: 7
Registered: August 2009
Junior Member
Executing: SELECT ASCIISTR(gmaid) FROM f0901;
Returns: \3939\3030\3032\3231
Re: ORA-12704: Character set mismatch [message #429457 is a reply to message #429456] Tue, 03 November 2009 23:14 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Returns: \3939\3030\3032\3231
CHR(39) = '9'
CHR(30) = '0'
CHR(31) = '1'
CHR(32) = '2'
Re: ORA-12704: Character set mismatch [message #429459 is a reply to message #429453] Tue, 03 November 2009 23:21 Go to previous messageGo to next message
JoeyD
Messages: 7
Registered: August 2009
Junior Member
Yes, \3939\3030\3032\3231 represents 99000221.

Note, the view f0901 references a SQL Server table. From initial research, the suggestion was to use TRANSLATE in the view on the gmaid field, but this will be time consuming to implement (we have many views) and may impact performance. There must be an easier "global" way of resolving this.

Do you think changing NLS_NCHAR_CHARACTERSET to UTF8 would make any difference?
Re: ORA-12704: Character set mismatch [message #429460 is a reply to message #429459] Tue, 03 November 2009 23:26 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You started with V9 &ASCII characterset.
You now have V10 & different characterset.

EXACTLY how did you get from start to now?
Re: ORA-12704: Character set mismatch [message #429462 is a reply to message #429453] Tue, 03 November 2009 23:53 Go to previous message
JoeyD
Messages: 7
Registered: August 2009
Junior Member
Apologies, I don't know what you are asking. Our 9i dataset was US7ASCII, which is a SUBSET of WE8ISO8859P1, so there should not be a problem there.

The issue we have is conversion of the NCHAR data (sqlserver) to VARCHAR2 (oracle).
Previous Topic: MAX() resulting in random -2112 errors
Next Topic: ALL_OBJECTS vs ALL_SEQUENCES
Goto Forum:
  


Current Time: Fri Dec 02 16:33:33 CST 2016

Total time taken to generate the page: 0.17012 seconds