Re: Database with different character set problems

David wrote:

> Hi all,
>
> I have two database (Oracle 8.1.7 in Solaris platform), database testing1
> is UTF8 character set, database testing2 is ZHT16BIG5 character set.
>
> When I create a database link from testing1 to testing2, and execute
> (testing_chinese is a table with varchar2(10)):
>
> select * from testing_chinese_at_testing1;
>
> I found that only first three characters is selected in database
> testing1. I know that this is because in ZHT16BIG5, each character is
> represented by 2 byte. However, in UTF8, each character (chinese) is
> represented by 3 bytes. So only the first characters is selected. The above
> select statement is issued in SQL*Plus on Windows platform.
>
> How can I retrieve all chinese characters in database testing1?
>
> On the other hand, I found that if database testing1 is 9i (character set
> is UTF8), I can retrieve all characters from database testing2 (testing2 is
> still 8.1.7, ZHT16BIG5). I don't understand why 9i can retrieve all
> characters. Is the UTF8 in 9i is different from UTF in 8i?
>
>
> Thanks,
> David
>
>
>

AL32UTF8 is the preferred 9i one - did not exist in 8i. So - yes they are different.

You say the columns are varchar2(10) - I take it you mean varchar2(10 BYTE), as you leave out the specification. Use varchar2(10 CHAR) instead - it will ensure you can store 10 Characters, whether they are stored as double or triple byte characters.
Within the limit of a varchar column, that is, i.e. 4000 bytes max.

BTW, what do you mean by the first 3 characters - I don't quite understand the problem, other that that character sets seems to be an issue of some sort.
Are you saying the client testing1 only retrieves a part of the strings?

-- 

Regards,
Frank van Bortel