Re: Wrong data in 8i mview refreshed from 10g db with different characterset
- From: Maureen English <sxmte@xxxxxxxxxxxxxxxx>
- To: Martin Berger <martin.a.berger@xxxxxxxxx>
- Date: Wed, 17 Sep 2008 11:44:42 -0800
Martin,
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
and
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.
Thanks again!
- Maureen
Martin Berger wrote:
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
