Home » SQL & PL/SQL » 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 Go to next message
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 #273232 is a reply to message #273216] Tue, 09 October 2007 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does SQL*Plus supports chinese characters?
It does not support all MSWIN1252 characters, I doubt it does it for chinese but who knows?

Regards
Michel
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 Go to previous messageGo to next message
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 #273296 is a reply to message #273216] Tue, 09 October 2007 22:06 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
post CUT & PASTE results from

SELECT ASCIISTR(NAME) from TEST;

-- bad choice of column_name "NAME"
Re: How can I view the Chinese data in my database? [message #273332 is a reply to message #273296] Wed, 10 October 2007 00:40 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

SQL> SELECT ASCIISTR(NAME) from TEST;

ASCIISTR(NAME)
--------------------------------------------------------------------------------
????

Re: How can I view the Chinese data in my database? [message #273334 is a reply to message #273292] Wed, 10 October 2007 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think on your OS and version, SQL*Plus can't display chinese characters.
Make a spool, re-execute the statement and open the spool file with an editor that supports chinese characters. I bet you'll get the correct characters.

Regards
Michel
Re: How can I view the Chinese data in my database? [message #273434 is a reply to message #273334] Wed, 10 October 2007 08:13 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi,Michel.
Here are the picture I got from the your tips.But it doesn't show me the correct characters.

This is what I typied in sqlplus:
SQL> select * from test;

no rows selected

SQL> insert into test
  2  values (1,'你好吗');

1 row created.

SQL> spool on
SQL> spool /home/oracle/godbless
SQL> select * from test;

        ID NAME
---------- ------------------------------
         1 ??????

SQL> spool off




This spool file I open it with vi,but it can't work.

SQL> select * from test;

        ID NAME 
---------- ------------------------------ 
         1 ?????? 

SQL> spool off
#This is what I typy in terminal
你好啊!
So vi is support Chinese Characters ,but it doesn't work.


Here are the picture:
./fa/3212/0/

Any ideas?
Thanks,
snowball
Re: How can I view the Chinese data in my database? [message #273436 is a reply to message #273434] Wed, 10 October 2007 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it seems your SQL*PLus does definitively not support chinese characters.

Regards
Michel
Re: How can I view the Chinese data in my database? [message #273446 is a reply to message #273216] Wed, 10 October 2007 09:15 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
The fact that
SQL> SELECT ASCIISTR(NAME) from TEST;
does not display meaningful string leads me to conclude that the actual non-ASCII characters are NOT actually being stored in the database.
In other words the INSERT is not working as expected/intended.
Re: How can I view the Chinese data in my database? [message #273472 is a reply to message #273446] Wed, 10 October 2007 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh yes!
Post the query against v$nls_parameters.

Regards
Michel
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 Go to previous messageGo to next message
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 #273571 is a reply to message #273446] Thu, 11 October 2007 00:00 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

anacedent wrote on Wed, 10 October 2007 09:15

The fact that
SQL> SELECT ASCIISTR(NAME) from TEST;
does not display meaningful string leads me to conclude that the actual non-ASCII characters are NOT actually being stored in the database.
In other words the INSERT is not working as expected/intended.


Is that means the database character set is not the same with sqlplus?And I did insert the Chinese data,if not what should I do?
Re: How can I view the Chinese data in my database? [message #273572 is a reply to message #273472] Thu, 11 October 2007 00:03 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Michel Cadot wrote on Wed, 10 October 2007 10:24

Oh yes!
Post the query against v$nls_parameters.

Regards
Michel




SQL> select * from v$nls_parameters;

PARAMETER                                                        VALUE
---------------------------------------------------------------- ---------------
NLS_LANGUAGE                                                     AMERICAN
NLS_TERRITORY                                                    AMERICA
NLS_CURRENCY                                                     $
NLS_ISO_CURRENCY                                                 AMERICA
NLS_NUMERIC_CHARACTERS                                           .,
NLS_CALENDAR                                                     GREGORIAN
NLS_DATE_FORMAT                                                  DD-MON-RR
NLS_DATE_LANGUAGE                                                AMERICAN
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.

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #273774 is a reply to message #273754] Thu, 11 October 2007 11:35 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://www.herongyang.com/gb2312/overview.html
1.) Find the Unicode codepoints for your characters, as well as the 2-byte storage value for GB2312-80 codepage
2.) Insert the characters using unistr() function.
3.) Confirm that the stored bytes are right using dump. (1010 for Decimal, 1016 for Hex).
4.) See if you can select the characters back successfully.

My U+20AC example may not be a good test case for you as it's a Euro sign and your database characterset may not define it.
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 Go to previous messageGo to next message
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 #273999 is a reply to message #273992] Fri, 12 October 2007 11:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You use a wrong syntax in unistr.
Use the syntax 3 posts above (\ and not 0x).

Regards
Michel

[Updated on: Fri, 12 October 2007 11:09]

Report message to a moderator

Re: How can I view the Chinese data in my database? [message #274004 is a reply to message #273999] Fri, 12 October 2007 12:06 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Oh,I am very HAPPY NOW!
I fixed the problem!
With this link:
http://download.oracle.com/docs/cd/B12037_01/server.101/b12170/ch12.htm#i1025728
I should have done this early.
Because you all told me it's about SQL client.
Yes,this issue is about SQL client.
Thanks everybody.
Especially andrew and Michel.
Hope we can be friends. :0

[Updated on: Fri, 12 October 2007 12:57]

Report message to a moderator

Re: How can I view the Chinese data in my database? [message #274006 is a reply to message #274004] Fri, 12 October 2007 12:27 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
congratulations on persisting when all your other classmates gave up!
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 Go to previous message
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

Previous Topic: sql developer querry
Next Topic: Using SQLPLUS on zOS to talk to remote database
Goto Forum:
  


Current Time: Mon Dec 05 12:53:50 CST 2016

Total time taken to generate the page: 0.11415 seconds