Re: Wrong data in 8i mview refreshed from 10g db with different characterset

Maureen,
This is the correct behaviour for the database. (Well, in fact it was not 100% correct during the time, you used US7ASCII on both DBs) 
Let me explain, what's going on:
First of all, there is an important component involved you did not mention yet: your client (sqlplus or whatever you use) and its NLS_* settings. (especially the character set!) 1) A database converts characters only if the client and the server does NOT have the same character set and the codepoint in both caractersets does not match. 
2) US7ASCII is only defined for the least 7 bit.

This brings you to some interresting situations:
A) If all components (client, master, replication) hast the same character set configured, you can insert any character you can enter in the client, it will not be interpreted or converted, not even checked and stored in the data block. At a select also nothing happens, it just got read from the block and sent to the client. Even if the character does not match into the character set (e.g. has 8 bits instead of 'allowed' 7) it's handled this way. B) If the client and the master has a different character set, either of them (prefered the client, but if it cannot, the DB) converts the chartacters to the destinations code point. the same vice versa. Oracle seems to use the 'nearest' good character, if a correct conversion is not possible. (I have not checked this fully)
In your situation, the client and master has either the same character set, or there is a conversion. So the characters get in the master corect. As the Replication is nothing more than a client-server communication, there is a character conversion. And as US7ASCII can not gather all differeent characters, i has to search for the 'nearest'.
So my summary on this is (with knowing that I had to guess a lot and could not proove my theory on your test-system) your previous setup (all US7ASCII) was well working but wrong, and you will have to switch your replication to a characterset which can hold all characters from WE8MSWIN1252. 

I hope, this helps.

best regards,
 Martin


--
Martin Berger martin.a.berger@xxxxxxxxx http://berx.at/ 

Am 17.09.2008 um 02:44 schrieb Maureen English:


We have an 8i database (US7ASCII characterset) with materialized views
being refreshed from a 10g database (was US7ASCII, just changed to WE8MSWIN1252).
Prior to converting the 10g database to WE8MSWIN1252, the data was displayed 
the same in both databases.

For example:

Langue et littérature français
resulted from a select statement in both databases when they were both US7ASCII. Now, the result is the same in the WE8MSWIN1252 database, but after I refreshed the materialized view, the result from the 8i database, which is still US7ASCII, 
is different:

Langue et litterature francais
The codepoints should have been the same in both databases, but for some reason, 
the refresh changed things.



The table in the 10g master (WE8MSWIN1252) database shows:
TEST> select dump(STVCIPC_DESC) from stvcipc where stvcipc_code='550100'; 

DUMP(STVCIPC_DESC)
------------------------------------------------------------------------------------------------------------------------------------
Typ=1 Len=30: 76,97,110,103,117,101,32,101,116,32,108,105,116,116,233,114,97,116,117,114,101,32,102,114,97,110,231,97,105,115
but the table in the 8i (US7ASCII) database shows different codepoints:
RPTT> select dump(STVCIPC_DESC) from stvcipc where stvcipc_code='550100'; 

DUMP(STVCIPC_DESC)
--------------------------------------------------------------------------------
Typ=1 Len=30: 76,97,110,103,117,101,32,101,116,32,108,105,116,116,101,114,97,116,117,114,101,32,102,114,97,110,99,97,105,115 ^ ^
If I select the codepoints from the 8i database, the characters show up correctly.... 

RPTT> select chr(101),chr(99),chr(233),chr(231) from dual;

C C C C
- - - -
e c é ç
Any ideas why the refresh of the materialized view wouldn't still contain the correct codepoints? 

- Maureen




--
http://www.freelists.org/webpage/oracle-l

Other related posts: