Re: NLS Problem : UTF8 ----> ZHT16BIG5

Aapr wrote:

> Hi All,
>
> We have a requirement wherein we will have a centralised transaction
> database for the Asian region. This database should be capable of
> storing data in multiple languages (as the transaction data in this
> database will be meant for multiple countries). Currently we are
> planning for English & Chinese data with option of Korean & Japanese
> in the future.
>
> After going thru Oracle's NLS documentation we decided to have this
> centralised database with Database character set as UTF8 (as it can
> supports multiple languages).
>
> For testing the data transfer in-between databases with different
> character sets, we transferred data from a UTF8 database to a
> Traditional Chinese database (ZHT16BIG5) database running on a Chinese
> Windows NT.
> But the tests for data transfer between this UTF database & a
> Traditional Chinese database (ZHT16BIG5) were not successful.
>
> The testing was done as follows:
> 1. Uploaded a text file containing Traditional Chinese characters on
> the UTF8 database running on Standard English Windows NT
> 2. After Uploading the Data, Transferred the Data to a Traditional
> Chinese database (ZHT16BIG5) database running on a Chinese Windows NT.
> The data was transferred to the Chinese database by a PowerBuilder
> application running on the English Windows machine. The data was
> pumped into the Chinese Database from the English Database ( and was
> not pulled by the Chinese DB from the English DB)
>
> After the above steps were completed, we logged on to the Chinese DB
> using SQLPlus on the same Chinese machine. We queried the table to
> which the data was pumped. The Query returned the string with "?" for
> almost all characters in the string. The data type of the column into
> which the data was pumped is VarChar2.
>
> After the above failure, we logged into the English UTF8 DB from the
> Chinese Windows SQLPlus & we queried the database table into which the
> Chinese data was uploaded ( in the step 1 from above). Still we got
> the string with the same "?" question marks. Next we tried the convert
> function with
> Select (Column A, UTF8, ZHT16BIG5) from Table B;
> and we got Chinese Text. But after looking carefully we found that the
> text differed from the original text that was in the file. Most of the
> characters displayed on the screen were different from those present
> in the file.
>
> What did we miss out ?
>
> Any ideas why this happened or how to resolve this issues. Any help is
> appreciated.
>
> TIA,
>
> Details of the two databases...............
>
> DB1 - Centralised DB
> - OS : Standard English Windows NT
> - Standard Oracle 8.1.5 Installation with NLS_CHARACTERSET and
> NLS_NCHAR_CHARACTERSET = UTF8
> - No Changes made to Oracle Client/Net8
>
> DB2 - Test DB for Chinese language
> - OS : Traditional Chinese Windows NT
> - Standard Oracle 8.1.6 Installation with NLS_CHARACTERSET and
> NLS_NCHAR_CHARACTERSET = ZHT16BIG5
> - No Changes made to Oracle Client/Net8
>
> Dataflow :
>
> Chinese Characters in File -----> Uploaded by PB program running on
> Standard English Windows NT to a UTF8 DB -----> Pumped into ZHT16BIG5
> DB by a PB program running on Standard English Windows NT (on the same
> machine containing the UTF8 DB)

If Oracle 8.1.5 then you need to migrate to 8.1.7.3 or above. My recollection is that there was a UTF8 bug in 8.1.5 and 8.1.6. Check on metalink to confirm whether that is the problem.

Daniel Morgan Received on Mon Jun 24 2002 - 17:43:25 CEST