WE8ISO8859P1 to AL32UTF8, error on some characters
94948Jun 24 2008 — edited Jun 25 2008Hello!
We're moving from 9i WE8ISO8859P1 to 11g AL32UTF8.
We've checked the sizes of all columns and recreated the tables and such to hold all the new data (since it will be larger than before for som rows).
But we have a, quite small, but possible big problem:
We find that 2 characters are converted from the source to the destination character set wrongly. Possible more characters, but only 2 identified so far.
Everyone working with AL32UTF8 knows that the regular ASCII characters below code 127 remans at this decimal code in AL32UTF8. And other (>127) are moved into different UTF-8 character groups (either, 2, 3 or 4 bytes long).
Most of our "special" characters, that mainly consist of the swedish auml-characters (åäö ÅÄÖ) are moves correctly, together with other special characters. The resulting AL32UTF8 database shows them correctly.
But 2 characters; the "–" (em-dash, long -) and the "”" (right double qutation mark) gets converted to characters with decimal code 49814 and 49812. This shows up as error/unknown characters when output.
After some research I find that the correct values for these two are in fact 14846098 and 14844061, and when replaced in the database - REPLACE(s, CHR(49812), CHR(14846098)) - everything shows up as OK when output from the database.
Why is this? Is there anyone else experiencing the same problem? What other characters could be in the same, wrongly converted, group?
By the way, I've tried setting the NLS_LANG parameter to every possible value (WE8ISO8859P1, AL32UTF8 and UTF8) both when importing and exporting, and every possible combination of these, but with no luck.
Regards
Charlie Elgholm