Re: Character set while export and import drives me crazy

Jaap W. van Dijk wrote:
> Comments inline
>
> On Mon, 25 Apr 2005 10:19:07 +0200, Ralf Bender
<ralf.bender_at_arcor.de>
> wrote:
>
> >Hello,
> >
> >import and export are driving me crazy.
> >
> >I've got a source database ORA 7.3 which I exported first with a
> >character set in the enviroment variable US7ASCII. The original
database
> >was created with AMERICAN_AMERICA.US7ASCII.
>
> But what does it *really* contain? GERMAN_GERMANYWE8ISO8859P1
> characters?

It contains whatever is put into it. exp/imp will automatically convert characters, but other tools may not. Thus, it is simple to put 8 bit characters into a 7 bit database, then not see any problems until you use a tool like exp/imp.

> >
> >I will import this to a target database ORA 8.1 with a character set
of
> >WE8ISO8859P1.
> >
> >After importing i loose all german special characters.
>
> Oracle is liable to do a conversion when the charactersets are
> different. If all charactersets (source, environment, target) are
> identical, no conversion take place and the high-end bit will be
> exported and imported unharmed.

This is correct.

>
> >
> >I try a second export and set the enviroment at the source database
to
> >GERMAN_GERMANY.WE8ISO8859P1.
>
> Somewhere along the line a conversion takes place, but I'm not sure
> where: Oracle might convert before putting the data into the
> exportfile because charactersets of source database and environment
> differ, or put this fact into the file heading and use this to
convert
> while doing the import.

Depends on NLS settings, can happen either way. Also, it varies by version! See metalink Note: 15095.1 O9 is quite different.

>
> >
> >After importing I have also no german special characters.
> >
> >Can someone tells me what I'm doing worng here or what's the correct
way.
> >
>
> If your source database actually contains GERMAN_GERMANY.WE8ISO8859P1
> characters, fool Oracle by setting the environment and the target
> database to the source database value (AMERICAN_AMERICA.US7ASCII) ,
so
> no conversion takes place, and afterwards change the target database
> value to GERMAN_GERMANY.WE8ISO8859P1.

This should work, see metalink Note: 257736.1 for instructions on altering the database character set.

The lesson for all lurkers: Never use the 7 bit characterset for your database. I've seen entirely English apps get screwed up with exp/imp because they use eight bits in user-defined datatypes.

jg

-- 
@home.com is bogus.
"Didn't even obey my own .sig!" - Paul