Home » Other » Client Tools » How can I view the Chinese data in my database?
| How can I view the Chinese data in my database? [message #273216] |
Tue, 09 October 2007 10:04  |
snowball
Messages: 229
Registered: April 2006
Location: China
|
Senior Member |
|
|
Hi,all.
I am using Oracle 10g on CentOS 4.5.
I can type Chinese words in sqlplus command line,but after I inserted the Chinese data,it shows me the "??" marks.
This is some information:
[oracle@localhost ~]$ whoami
oracle
[oracle@localhost ~]$ locale
LANG=zh_CN.UTF-8
LC_CTYPE=zh_CN
LC_NUMERIC="zh_CN.UTF-8"
LC_TIME="zh_CN.UTF-8"
LC_COLLATE="zh_CN.UTF-8"
LC_MONETARY="zh_CN.UTF-8"
LC_MESSAGES="zh_CN.UTF-8"
LC_PAPER="zh_CN.UTF-8"
LC_NAME="zh_CN.UTF-8"
LC_ADDRESS="zh_CN.UTF-8"
LC_TELEPHONE="zh_CN.UTF-8"
LC_MEASUREMENT="zh_CN.UTF-8"
LC_IDENTIFICATION="zh_CN.UTF-8"
LC_ALL=
[oracle@localhost ~]$
Can somebody tell me how to fix it?
thanks,
snowball
|
|
|
|
|
|
| Re: How can I view the Chinese data in my database? [message #273292 is a reply to message #273216] |
Tue, 09 October 2007 21:50  |
snowball
Messages: 229
Registered: April 2006
Location: China
|
Senior Member |
|
|
SQL> insert into test
2 values (1,'你好');
1 row created.
SQL> select * from test;
ID NAME
---------- ------------------------------
1 ????
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
NAME NOT NULL VARCHAR2(30)
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ -------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16CGB231280
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ -------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.1.0
20 rows selected.
Oracle 9i in my windows xp support the Chinese data,but now I using CentOS 4.5,so I don't have that MS Characters.
Is that something wrong?
[Updated on: Tue, 09 October 2007 21:54] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How can I view the Chinese data in my database? [message #273516 is a reply to message #273472] |
Wed, 10 October 2007 14:23 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
The first place to start is to make sure that the byte values stored in Oracle are correct for your characterset.
Make sure your client NLS_LANG is compatible with your database characterset. You can display it like this:
SQL> @%NLS_LANG%
SP2-0310: unable to open file "AMERICAN_AMERICA.WE8MSWIN1252"
Once your data is in the table, dump it to see the byte values.
select id, name, vsize(name), dump(name, 1010) Decimal_bytes, dump(name, 1016) Hex_Bytes from test;
If the byte values represnt "?", then the characterset conversion failed during the insert - else it's a display issue retrieving it.
|
|
|
|
|
|
|
|
| Re: How can I view the Chinese data in my database? [message #273575 is a reply to message #273516] |
Thu, 11 October 2007 00:21 |
snowball
Messages: 229
Registered: April 2006
Location: China
|
Senior Member |
|
|
| andrew again wrote on Wed, 10 October 2007 14:23 |
The first place to start is to make sure that the byte values stored in Oracle are correct for your characterset.
Make sure your client NLS_LANG is compatible with your database characterset. You can display it like this:
SQL> @%NLS_LANG%
SP2-0310: unable to open file "AMERICAN_AMERICA.WE8MSWIN1252"
Once your data is in the table, dump it to see the byte values.
select id, name, vsize(name), dump(name, 1010) Decimal_bytes, dump(name, 1016) Hex_Bytes from test;
If the byte values represnt "?", then the characterset conversion failed during the insert - else it's a display issue retrieving it.
|
SQL> @%NLS_LANG%
SP2-0310: unable to open file "%NLS_LANG%.sql"
SQL> select id, name, vsize(name), dump(name, 1010) Decimal_bytes, dump(name, 1016) Hex_ Bytes from test;
rows will be truncated
ID NAME VSIZE(NAME) DECIMAL_BYTES
---------- ------------------------------ ----------- --------------------------
1 ?????? 6 Typ=1 Len=6 CharacterSet=ZHS16CGB231280: 63,63,63,63,63,63
This is an interesting problem,all my classmates have met this problem ,but at last ,they all gave up.
But I am lucky to have so many kind-hearted friends here.
I got that result from sqlplus,then I found the characterset is Chinese,but why it can support the Chinese Data?
|
|
|
|
| Re: How can I view the Chinese data in my database? [message #273582 is a reply to message #273572] |
Thu, 11 October 2007 00:55 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Try looking up the UTF-8 codepoints for your characters and seeing if you can successfully insert those into your table. Example: UTF-8 codepoint for Euro sign is U+20AC, so to insert a Euro without literally quoting it, you would insert into test values (1, unistr('\20AC'));
If this works and the dump() confirms the correct byte value is stored for ZHS16CGB231280 then it seems your client NLS setting isn't compatible with your database characterset. You dont say what your client NLS_LANG is. Try setting NLS_LANG=AMERICAN_AMERICA.UTF8 before starting sqlplus.
The @%NLS_LANG% trick to display the NLS_LANG setting in effect might only work from DOS cmd window.
[Updated on: Thu, 11 October 2007 01:05] Report message to a moderator |
|
|
|
| Re: How can I view the Chinese data in my database? [message #273754 is a reply to message #273582] |
Thu, 11 October 2007 10:15 |
snowball
Messages: 229
Registered: April 2006
Location: China
|
Senior Member |
|
|
[oracle@localhost db_1]$ export NLS_LANG=AMERICAN_AMERICA.UTF8
[oracle@localhost db_1]$ echo $NLS_LANG
AMERICAN_AMERICA.UTF8
I am sorry the code is so mass.
SQL> insert into test values (1, unistr('\20AC'));
1 row created.
SQL> select id, name, vsize(name), dump(name, 1010) Decimal_bytes, dump(name, 1016) Hex_ Bytes from test;
ID NAME VSIZE(NAME)
---------- ------------------------------------------------------------------------------------------ -----------
DECIMAL_BYTES
------------------------------------------------------------------------------------------------------------------------
HEX_BYTES
------------------------------------------------------------------------------------------------------------------------
1 \u951b\uffff 2
Typ=1 Len=2 CharacterSet=ZHS16CGB231280: 163,191
Typ=1 Len=2 CharacterSet=ZHS16CGB231280: a3,bf
It seems that it can insert a character ,but when i pasted that character on this post,it changed to "\u951b\uffff".
So I got a picture for you and when inserting Chinese Characters ,a error came out.
[Updated on: Thu, 11 October 2007 10:25] Report message to a moderator |
|
|
|
|
|
| Re: How can I view the Chinese data in my database? [message #273992 is a reply to message #273754] |
Fri, 12 October 2007 10:54 |
snowball
Messages: 229
Registered: April 2006
Location: China
|
Senior Member |
|
|
{code}
SQL> insert into test
2 values (1,unistr('0xB0AE'));
1 row created.
SQL> select id,name,vsize(name),dump(name,1010) Decimal_b ytes,dump(name,1016) Hex_Bytes from test;
ID NAME VSIZE(NAME)
---------- ------------------------------ -----------
DECIMAL_BYTES
--------------------------------------------------------- -----------------------
HEX_BYTES
--------------------------------------------------------- -----------------------
1 0xB0AE 6
Typ=1 Len=6 CharacterSet=ZHS16CGB231280: 48,120,66,48,65, 69
Typ=1 Len=6 CharacterSet=ZHS16CGB231280: 30,78,42,30,41,4 5
[/code]
what if the Len is not right?
|
|
|
|
|
|
|
|
|
|
| Re: How can I view the Chinese data in my database? [message #274012 is a reply to message #274004] |
Fri, 12 October 2007 12:56 |
snowball
Messages: 229
Registered: April 2006
Location: China
|
Senior Member |
|
|
This is what I did:
First,I read the whole link:
http://download.oracle.com/docs/cd/B12037_01/server.101/b12170/ch12.htm#i1025728
Second,change NLS_LANGUAGE,NLS_TERRITORY TO WHAT THE LANGUAGE AND COUNTRY YOU WANT.
For me is:
SQL> alter session set NLS_LANGUAGE='SIMPLIFIED CHINESE';
??????
SQL> ALTER SESSION SET NLS_TERRITORY='CHINA';
??????
don't worry about the "???' marks.
Third,I used this command to see all the parameters of nls:
select * from v$nls_parameters;
SQL> select * from v$nls_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_LANGUAGE SIMPLIFIED CHIN
NLS_TERRITORY CHINA
NLS_CURRENCY ￥
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE SIMPLIFIED CHIN
NLS_CHARACTERSET ZHS16CGB231280
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI
NLS_DUAL_CURRENCY ￥
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected.
The datas are what we wanted:
NLS_LANGUAGE
NLS_TERRITORY
NLS_CHARACTERSET
Four,set the NLS_LANG.
NLS_LANG has the syntax:
NLS_LANG = language_territory.charset
use the terminal then typied the command(if you are using bash):
export NLS_LANG=($NLS_LANGUAGE)_($NLS_TERRITORY).($NLS_CHARACTERSET)
For me is:
[oracle@localhost ~]$ export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280"
The last thing,you gonna do is to have a cup of coffie and type "sqlplus",
enter the sqlplus and login to the database.
[oracle@localhost ~]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 10月 13 00:53:24 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
请输入用户名: system
输入口令:
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from test;
未选定行
SQL> insert into test
2 values (1,'你好');
已创建 1 行。
SQL>
SQL> select * from test;
ID NAME
---------- ------------------------------
1 你好
Hope it can help somebody,
thanks again,andrew and Michel.
snowball
[Updated on: Fri, 12 October 2007 18:37] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Fri Jan 03 09:39:34 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
