I am on HP UNIX 11i using Oracle 9.2.0.5.

I had a database in database character set US7ASCII. I recently
converted it to database character set UTF8. Since I do not have any
nchar or nvarchar2 columns, national character set is irrelevant even
though it was UTF8 to begin with.

Export was done using NLS_LANG=AMERICAN_AMERICA.US7ASCII and same
setting was used for import.

During import some rows got rejected because column lengths of some
varchar2 column will not fit UTF8 chacater set. I know UTF8 needs more
bytes if character is outside of ASCII. My question is since I had
US7ASCII database to begin with, why my rows got rejected, since UTF8
is a superset
of US7ASCII.

Looking at failed rows generated during import, I did notice that some
rows had characters such as Ö (O with two .. on top), R with a
trademark on top of it. If my database character set was ASCII, how
this character was inserted in the first place. What is the meaning of
US7ASCII if my database had these characters.
All columns with error are varchar2 columns.

Appreciate your insight.

Thanks a lot.

dbaplusplus@hotmail.com wrote:
> I am on HP UNIX 11i using Oracle 9.2.0.5.
>
> I had a database in database character set US7ASCII. I recently
> converted it to database character set UTF8. Since I do not have any
> nchar or nvarchar2 columns, national character set is irrelevant even
> though it was UTF8 to begin with.
>
> Export was done using NLS_LANG=AMERICAN_AMERICA.US7ASCII and same
> setting was used for import.
>
> During import some rows got rejected because column lengths of some
> varchar2 column will not fit UTF8 chacater set. I know UTF8 needs more
> bytes if character is outside of ASCII. My question is since I had
> US7ASCII database to begin with, why my rows got rejected, since UTF8
> is a superset
> of US7ASCII.
>
> Looking at failed rows generated during import, I did notice that some
> rows had characters such as Ö (O with two .. on top), R with a
> trademark on top of it. If my database character set was ASCII, how
> this character was inserted in the first place. What is the meaning of
> US7ASCII if my database had these characters.
> All columns with error are varchar2 columns.
>
> Appreciate your insight.
>
> Thanks a lot.


Does any one hae insight in this? Apprecaite your answer.

The database can store 8 bit characters, even if you are using a 7 bit
ASCII characterset, if the tool you use to insert the characters allows
it, which most do.

exp/imp "helpfully" handles translations between character sets. So,
if you tell it that it exported a 7 bit set, and is importing into an 8
bit set, it will likely truncate that extra bit.

See
http://groups.google.com/group/comp....e=source&hl=en
and look at the docs about NLS and character set conversion on
metalink, as well as search asktom.oracle.com for "exp imp characterset
conversion" (without the quotes). You basically want to fool oracle
into not doing the conversion.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/uniont...b10zubkis.html

Thanks a lot, that must be it.