1

We have two Oracle DB's that are set to:

SQL> SELECT USERENV ('language') FROM DUAL;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

The rest of our instances are set as follows:

SQL> SELECT USERENV ('language') FROM DUAL;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.WE8ISO8859P1

This is creating all sorts of problems in data being sent from one db to another. Is it possible to convert the two "non-standard" db's to match the others. If so, how?

jeffspost
  • 139
  • 3
  • 5
  • 14

1 Answers1

2

well, the first character set is Unicode, which basically supports all languages. The second one ("P1") is western-Europe ASCII, supporting English and some extra western characters.

So,

  • in what languages is your data? what languages will you need to support?
  • Do you want to convert UNICODE db to ASCII db or the other way around?

Converting from WE8...P1 to Unicode can easily be done by exporting (setting NLS_LANG to WE8..P1), creating a new, empty database, and importing (again, setting NLS_LANG to WE8..P1). However, converting to Unicode has some side effects, especially regarding to limits of any varchar2 columns - if it was varchar2 (20), it usually means 20 bytes, so when using Unicode 20 bytes can hold less (non-English) characters... So, to avoid data truncation, some columns might need to be enlarged... All the details are here:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28298/ch11charsetmig.htm

Converting from UTF8 to WE8..P1 is again, doing export with NLS_LANG=WE8...P1 (you will now lose all characters that don't map into P1 ASCII, like Arabic, Chinese etc), creating an empty We8..P1 database and importing the data with NLS_LANG=WE8ISO8859P1.

Ofir Manor
  • 731
  • 3
  • 6
  • +1, but just a clarification on converting WE8..P1 to UTF8: Your new, empty DB must be created as UTF8, correct? – DCookie Jul 20 '09 at 18:21
  • absolutely... you create a new, empty DB with the required character set and import the data from the "wrong" character set/ – Ofir Manor Jul 22 '09 at 16:40