Guys I am puzzled,
I have an oracle backup (through export dump) from a system which had the following settings:
OS: Windows 7 - no longer available
Collation/Locale: English,
Oracle XE 10g version: NLS_LANG set as AMERICAN_AMERICA.WE8MSWIN1252 in registry settings, language settings AL32UTF8 CHAR and AL16UTF16 NCHAR settings
Primavera P6: Greek and English characters
From dump file I moved to a new pc with the following settings:
OS: Windows 7
Collation/Locale: Greek - need to keep this as is,
Oracle XE 10g version: NLS_LANG set as GREEK_GREECE.EL8MSWIN1253 in registry settings, language settings AL32UTF8 CHAR and AL16UTF16 NCHAR settings
Before importing data I read various Oracle documents on how to import data with minimum data conversion like the following
NLS considerations in Import/Export - Frequently Asked Questions [ID 227332.1]
NLS_LANG Explained (How does Client-Server Character Conversion Work) [ID 158577.1]
The correct NLS_LANG in a Windows Environment [ID 179133.1]
From these documents along many things I kept the following under close consideration:
1. Regarding NLS_LANG : on both export and import try to use the character set of the source database.
and
2. To debug display problems to check with sql developer.
So I opened a command prompt and set NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 and then run the import command.
The end result is puzzling. After import of the data I noticed the following:
1. From P6 and sqldeveloper cannot understand greek chars in old data (imported data)
2. If I add greek chars in P6 I can see them correctly in P6 but not in sqldeveloper
3. If I add greek chars in sqldeveloper then I can see them correctly in both P6 & sqldeveloper
4. Then I experimented by changing NLS_LANG in registry to AMERICAN_AMERICA.WE8MSWIN1252
a. I can now see old data in P6 client but not data inserted in steps 2&3. Also I cannot see new data created in sqldeveloper
b. I cannot see old data in sqldeveloper but can see data inserted in steps 2&3 and new data created in P6 and sqldeveloper
What am I missing here? What is considered a "safe" configuration for not losing data? For example if I keep NLS_LANG in registry to AMERICAN_AMERICA.WE8MSWIN1252 and Collation to Greek will I have issues with trancated chars in future import/exports?
regards
TN