ORA-12704: Character set mismatch [message #429453] |
Tue, 03 November 2009 22:49  |
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 #429459 is a reply to message #429453] |
Tue, 03 November 2009 23:21   |
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 #429462 is a reply to message #429453] |
Tue, 03 November 2009 23:53  |
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).
|
|
|