How can i convert from USASCII7 to WE8ISO8859P1 ?
Ulrich Knecht
I have a question concerning character set conversion: Our customer
uses an oracle 8.0.5 Database (on Linux) using USASCII7 character set.
He wants to migrate to oracle 8i (also on Linux) using iso character set
(WE8ISO8859P1). The original data contains german umlauts (ä, ö, ü, ß)
which are being displayed correctly although they are not contained in 7
bit ASCII (Don't ask me why, but it works).
How can I migrate the data in a way that what is, e.g. an "ä"
(a-umlaut) in the old data base still appears as an "ä" in the new one?
I made the following attempts, all of which didn't have the required
result:
- export without NLS_LANG set (which should default to US ASCII 7
bit), import with
NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1
- export / import without NLS_LANG set
- export / import with NLS_LANG=NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1
I'd be very thankfull if anyone could help me solve this problem.
Ulrich
fro_doe
database as is from 8.0 to 8i, then using the following command
to change the character set:
ALTER DATABASE [<db_name>] CHARACTER SET <new_character_set>;
However, it sounds pretty curious to me that the database
character set is US7ASCII, yet extended ASCII characters are
being stored and retrieved correctly. What does this query
return?
select value from nls_database_parameters
where parameter='NLS_CHARACTERSET';
Are you sure the data is not coming through a dblink or is stored
in a NCHAR column?
Regards, Doug.
Ulrich Knecht <kne...@thales.de> wrote:
>Hello,
> I have a question concerning character set conversion: Our
customer
>uses an oracle 8.0.5 Database (on Linux) using USASCII7
character set.
>He wants to migrate to oracle 8i (also on Linux) using iso
character set
>(WE8ISO8859P1). The original data contains german umlauts (=E4,
=F6, =FC,=
> =DF)
>which are being displayed correctly although they are not
contained in 7
>bit ASCII (Don't ask me why, but it works). =
>
> How can I migrate the data in a way that what is, e.g. an
"=E4"
>(a-umlaut) in the old data base still appears as an "=E4" in the
new one?=
>
> I made the following attempts, all of which didn't have the
required
>result:
>
> - export without NLS_LANG set (which should default to US
ASCII 7
>bit), import with =
>
> NLS_LANG=3DGERMAN_GERMANY.WE8ISO8859P1
>
> - export / import without NLS_LANG set
>
> - export / import with
NLS_LANG=3DNLS_LANG=3DGERMAN_GERMANY.WE8ISO8859P=
>1 =
>
>
>I'd be very thankfull if anyone could help me solve this
problem.
>
> Ulrich
>
>
Got questions? Get answers over the phone at Keen.com.
Up to 100 minutes free!
http://www.keen.com
Ulrich Knecht
thank you for the hint.
> select value from nls_database_parameters
> where parameter='NLS_CHARACTERSET';
actually gives US7ASCII. In the meantime I tried some more combinations
of NLS_LANG at import/export, still without success. The only hope left
seems to be the ALTER DATABASE statement you proposed. But I have to set
up a test environment for that to make sure it turns out as expected
before exercising it on the "real" database.
Anyhow, I will have to do an import/export afterwards as the database
is going to be moved to another disk/tablespace layout.
Thanks a lot,
Ulrich
Jim Gregory
We are going through the same agony attempting to convert a US7ASCII
database to an 8 bit W.... We have some Korean and Taiwan databases that,
while they are in US7ASCII, have extended 8 bit characters. We really don't
know how they got there.
Like you, I have tried every combination of conversion, export/import, and
NLS settings that I could identify and nothing worked. The only thing that
seemed to work was a very involved process of
1) create a WE8ISO8859P1 database
2) set up a DB link to the US7ASCII DB
3) Then in PL/SQL or C or some other language
4) grab the row and field with the 8 bit data
5) parse each character out with something like the dump() sql function
6) concatenate the field back together using the chr() sql function for each
of
the characters parsed out in step 5
7) store the row in the WE8ISO8859P1 database
I too would like to see someone come up with a better solution than this as
it's going to be a real pain in the a__ to get these databases converted to
an 8 bit language. Luckily for us not all character fields have 8 bit data
in them and we'll only have to use the above for those that do.
--
Jim Gregory
Principal Consultant for Keane, Inc.
Currently assigned to NCR
"Opinions are my own and do not reflect
those of Keane or my clients"
Ulrich Knecht <kne...@thales.de> wrote in message
news:394F9671...@thales.de...
Hello,
I have a question concerning character set conversion: Our customer
uses an oracle 8.0.5 Database (on Linux) using USASCII7 character set.
He wants to migrate to oracle 8i (also on Linux) using iso character set
(WE8ISO8859P1). The original data contains german umlauts (ä, ö, ü, ß)
which are being displayed correctly although they are not contained in 7
bit ASCII (Don't ask me why, but it works).
How can I migrate the data in a way that what is, e.g. an "ä"
(a-umlaut) in the old data base still appears as an "ä" in the new one?
I made the following attempts, all of which didn't have the required
result:
- export without NLS_LANG set (which should default to US ASCII 7
bit), import with
NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1
- export / import without NLS_LANG set
- export / import with NLS_LANG=NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1
Ulrich Knecht
> (Why do you post your question to many news groups?)
I'm sorry, I wasn't shure which one was the right one for this "queer"
topic. I'm aware that crossposting ist not polite. Sorry. Just so much
more: thank you for your kind advice.
As it turned out, a previous answer to my posting led to a solution by
executing ALTER DATABASE CHARACTER SET on the old database (I explained
that in detail in my reply to that answer).
Anyhow, thank you!
Ulrich
Ulrich Knecht
the ALTER DATABASE CHARACTER SET statement really did the job. It
appears, the umlauts our customer had in his database were in fact
iso-characters. Just that the database "thought" it contained only
US7ASCII characters. After altering the old database I was able to
export the data correctly as WE8ISO8859P1 and reimport it into the new
database.
Just for the sake of completeness, here is what I did:
1. Start the old database in exclusive mode and alter the character
set:
(according to the "Oracle8i National Language Support Guide")
$ svrmgrl system/<passwd of system>
SVRMGR> SHUTDOWN IMMEDIATE
SVRMGR> STARTUP MOUNT
SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SVRMGR> ALTER DATABASE OPEN;
SVRMGR> ALTER DATABASE CHARACTER SET WE8ISO8859P1;
SVRMGR> SHUTDOWN IMMEDIATE
SVRMGR> STARTUP
SVRMGR> QUIT
2. Set the NLS_LANG parameter and do the export:
$ NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1
$ export NLS_LANG
$ exp system/<passwd of system>
3. ftp the expdat.dmp file to the new system, set NLS_LANG
and do the import.
Thank you so much!
Ulrich