Re: Wrong data in 8i mview refreshed from 10g db with different characterset
Date: Wed, 17 Sep 2008 11:44:42 -0800
Thank you...the light bulb just went on!
I tried a test using sqlplus on the 8i server with my NLS_LANG variable set to AMERICAN_AMERICA.US7ASCII, then another test with it set to AMERICAN_AMERICA.WE8MSWIN1252. I just created a table by selecting from the 10g database where the characterset was changed to WE8MSWIN1252. Neither one cause the 'correct' data to appear in the 8i database.
Your explanation makes perfect sense, though. The key is that US7ASCII is only defined for the least 7 bits.
code points for the two 'incorrect' characters are
233 in the 10g database, coming across as 101 in the 8i database
231 in the 10g database, coming across as 99 in the 8i database
233 = 11101001 (if my binary is correct) and the least 7 bits are 1101001, which converts to 101!
Subtract 2 from 233 to get 231 and 2 from 101 and you get 99.
That completely explains my problem.
I did understand that the conversion should take place okay, but I did not know that if both databases were the same characterset that there would not be any conversion. Also, I guess that I just overlooked the fact that the 7 in US7ASCII was meaningful.
Martin Berger wrote:
> 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.