Re: Database with different character set problems

    yes, i can only retrieve part of the string. For example, assume in testing2, the whole record is ABCDE. I can only retrieve ABC with the "select * from testing_chinese_at_testing2" in database testing1. I think this is due to the character set conversion. This is because in testing2, character set is ZHT16BIG5, and each character is represented by 2 bytes (so 5 characters need 10 bytes storage). In testing1, character set is UTF8, and each chinese character is represented by 3 bytes. (so if 10 bytes, only store 3 characters) I don't know whether the above explanation is correct. And also want to know is it possible to retrieve all characters in testing1 with the same SQL statement?

THanks,
David

"Frank van Bortel" <fvanbortel_at_netscape.net> wrote in message news:c0je4r$tgm$1_at_news1.tilbu1.nb.home.nl...
> 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
>
Received on Fri Feb 13 2004 - 22:09:44 CST