Character set while export and import drives me crazy
Ralf Bender
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.
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.
I try a second export and set the enviroment at the source database to
GERMAN_GERMANY.WE8ISO8859P1.
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.
thx
ralf
Frank van Bortel
Actually, I can hardly believe you had *any* special character in
that databse, or it was not *created* with US7ASCII.
Special characters are in the extended characterset, which requires
the use of the 8th bit; obviously, something US7ASCII doesn't
allow.
If the 7.3 databse *did* have special characters, export using
AMERICAN_AMERICA.US7ASCII, and import using that, too.
You should *not* have warnings about characterset conversions!
--
Regards,
Frank van Bortel
Ralf Bender
I don't really understand what happens here.
I create a new database with US7ASCII and run the importfile also in
US7SCAII.
After that, I changed the characterset of the whole database to
WE8ISO8859P1 and I see german special characters!
But, how can i be sure that only the special characters are changed and
not other data....
Frank van Bortel
and not in e.g. long raw or long columns?
Long would suffer from characterset conversions; long raw would not.
Ralf Bender
mmmhh, I'm not sure. The old DB got 10.000 tables and don't really know
all of them and their data.
So, I think i have to leave it in US7ASCII. Changing the enviroment
variable of a client to Oracle standard will also bring up my special
characters... that's nice.
But, if I do so I got a new problem with my clients. All existing
databases works with client enviroment GERMANY_GERMAN.WE8ISO8859P1. And
that won't show the correct characters.
Do you think/know it's enough to change the session variable with alter
session:
alter session set nls_territory = AMERICA;
alter session set nls_language= AMERICAN;
ralf
Frank van Bortel
[snip!]
>>>
>>
>> Are you sure the special characters are stored in varchar2 columns,
>> and not in e.g. long raw or long columns?
>> Long would suffer from characterset conversions; long raw would not.
>
>
> mmmhh, I'm not sure. The old DB got 10.000 tables and don't really know
> all of them and their data.
> So, I think i have to leave it in US7ASCII. Changing the enviroment
> variable of a client to Oracle standard will also bring up my special
> characters... that's nice.
>
> But, if I do so I got a new problem with my clients. All existing
> databases works with client enviroment GERMANY_GERMAN.WE8ISO8859P1. And
> that won't show the correct characters.
>
> Do you think/know it's enough to change the session variable with alter
> session:
> alter session set nls_territory = AMERICA;
> alter session set nls_language= AMERICAN;
>
As you are not sure what is going on, I most
certainly don't know, either.
Based on that, I can only suggest: give it a try,
it may work. And it won't harm, as long as you
only read data, not alter or insert
Jaap W. van Dijk
On Mon, 25 Apr 2005 10:19:07 +0200, Ralf Bender <ralf....@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?
>
>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.
>
>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.
>
>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.
>thx
>ralf
Joel Garry
Jaap W. van Dijk wrote:
> Comments inline
>
> On Mon, 25 Apr 2005 10:19:07 +0200, Ralf Bender
<ralf....@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