Oracle Version : 10.1.0.5
Aix Version : 5.3
We have a Database with WE8ISO8859P1 character set. Special characters like single quotes appear as inverted question marks. for Ex : " The caller didn¿t see Mike but heard him say to Tom that he wanted his book back. Mitchell reached over to get Thomas¿s book bag" . To Resolve this issue we need to migrate it to AL32UTF8.
When we ran CSSCAN utility it showed us data loss/Lossy on the source side(WE8ISO8859P1). In order to resolve this issue we first changed the WE8ISO8859P1 to WE8MSWIN1252 by following the steps mentioned in "Changing US7ASCII or WE8ISO8859P1 to WE8MSWIN1252 in 8i, 9i, 10g and 11g ( Doc ID 555823.1 )" . After changing the character set to WE8MSWIN1252 we ran the CSSCAN utility again and it showed us zero data loss. Then using DATAPUMP we exported the data from WE8MSWIN1252 and imported it into AL32UTF8 Character set database. Now when we insert new records with special characters they don't appear as inverted question marks which is good, But for the existing data that got copied from the source, the question marks did not go away. The single quotes still show as inverted question marks.
P.S :NLS considerations in Import/Export - Frequently Asked Questions (Doc ID 227332.1) doc says that NLS_LANG Does not have any effect on datapump. So I did not set the NLS_LANG During Datapump Export or Import.
Any Help would be greatly appreciated.
Thanks
Satya