NLS_LANG引起的中文乱码

--首先该参数设置为空
[oracle@db ~]$ echo $NLS_LANG

[oracle@db ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 3 09:37:27 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

查看一下数据库服务器的字符集，应用的是UTF8
SQL> col parameter format a50
SQL> col value format a150
SQL> set linesize 220
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                                   UTF8
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_NCHAR_CHARACTERSET                             UTF8
NLS_COMP                                           BINARY
NLS_LENGTH_SEMANTICS                               BYTE
NLS_NCHAR_CONV_EXCP                                FALSE

19 rows selected.
--无法正常输出中文
SQL> select '你好' from dual;

'????'
------------
????

SQL> exit;

将客户端字符集设置为ZHS16GBK
[oracle@db ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@db ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 3 09:38:01 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select '你好' from dual;

'你好'
------------
你好

SQL> select dump('你好') from dual;

DUMP('你好')
--------------------------------------------------------------------------
Typ=96 Len=6: 228,189,160,229,165,189

SQL> select to_char(228,'xx'),to_char(189,'xx'),to_char(160,'xx'),to_char(229,'xx'),to_char(165,'xx'),to_char(189,'xx') from dual;

TO_CHA TO_CHA TO_CHA TO_CHA TO_CHA TO_CHA
------ ------ ------ ------ ------ ------
 e4     bd     a0     e5     a5     bd

SQL> select to_number('e4bda0','xxxxxx'),to_number('e5a5bd','xxxxxx') from dual;

TO_NUMBER('E4BDA0','XXXXXX') TO_NUMBER('E5A5BD','XXXXXX')
---------------------------- ----------------------------
                    14990752                     15050173

SQL> select chr(14990752),chr(15050173) from dual;

CHR(14 CHR(15
------ ------
你     好

将客户端字符集设置成UTF8
[oracle@db ~]$ export NLS_LANG=AMERICAN_AMERICA.UTF8
[oracle@db ~]$ echo $NLS_LANG
AMERICAN_AMERICA.UTF8
[oracle@db ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 3 10:21:53 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select 'ab f;' from dual;

'ABF;
-----
ab f;
--不能输出中文了
SQL> select '你好' from dual;
ERROR:
ORA-01756: quoted string not properly terminated

SQL> select dump('你好') from dual;
ERROR:
ORA-01756: quoted string not properly terminated

--按照ZHS16GBK下的编码复原，对应的是?
SQL> select chr(14990752),chr(15050173) from dual;

CHR CHR
--- ---
???  ?￥?

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-688307/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15480802/viewspace-688307/