Character set conversion (re Metalink notes 260192.1 and 225938.1) - Why?

  • From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 22 Oct 2008 09:10:09 -0500

Good day list,

I filed this question with Oracle Support, but they have been slow in

Metalink note 260192.1 (*Changing WE8ISO8859P1/ WE8ISO8859P15 or
WE8MSWIN1252 to (AL32)UTF8*) says:
If there is any "Lossy" data then those rows contain code points that are
not currently stored correctly and they should be cleared up before you can
continue with the steps in this note. Please see the following note for
clearing up any "Lossy" data: Note
Character Set Healthcheck

The thought is that in order to resolve lossy data when converting from a
WE8ISO8859P1 (aka ISO) character set to a (AL32)UTF8 character set, one
first has to convert to WE8MSWIN1252 (aka, WIN)  (the superset of
WE8ISO8859P1). Why?

I know the reasons that Oracle gives via the many Metalink notes
They boil down to the fact that the ISO set does not contain as many
codepoints as the WIN set. While this is true, it is also true that the WIN
set has *all* the codepoints that the ISO set does (hence the definition of
a superset).

So in essence, when one converts the character set from ISO to WIN, the
codepoints never change. If you store an "unsupported" character, say, the
Euro (128) or left double quote (147) in an ISO database, the ascii value
will remain the same (128 and 147 respectively) when you "convert".

But why? If I export my data from a WE8ISO8859P1 database with NLS_LANG =
WE8MSWIN1252 and then turn around and import that into a AL32UTF8 database,
all my lossy data is automagically converted. And so it should be. So why
does Oracle documentation tell me that I have to clean up the lossy data
first, when a much easier and foolproof method is available? What am I

Simple example

Source: OSEE (EXPRT)
Target: OSEE (PUMP)


SQL> select VALUE from nls_database_parameters where PARAMETER =


SQL> create table oracle.lossy_test (a char(1));

Table created.

SQL> insert into oracle.lossy_test values (chr(128));

1 row created.

$> expdp directory=DPUMP dumpfile=EXPRT_lossy_test.dmp
logfile=EXPRT_lossy_test.log metrics=y TABLES=ORACLE.LOSSY_TEST


SQL > select VALUE from nls_database_parameters where PARAMETER =


SQL > show parameter seman
NAME                                 TYPE     VALUE
------------------------------------ -------- ------------------------------
nls_length_semantics                 string   CHAR

SQL > create table oracle.lossy_test (a char(1 CHAR));

Table created.

PUMP_SQL > select * from oracle.lossy_test;

no rows selected

$> impdp directory=DPUMP dumpfile=EXPRT_lossy_test.dmp
logfile=PUMP_lossy_test.log metrics=y TABLES=ORACLE.LOSSY_TEST

With a windows client (ie, sqlplusw), I can see that the Euro shows up just
fine in both databases.

Charles Schultz

Other related posts:

  • » Character set conversion (re Metalink notes 260192.1 and 225938.1) - Why?