NLS_LANG引起的SQLPLUS乱码和length长度不正确.

 创建一个实验表语句如下

SQL> create table test(id number,name varchar2(10));

当我们在SQLPLUS里面敲入下面的语句并回车执行的时候,SQLPLUS传到Oracle服务器端的数据实际上是经过OS系统默认编码后的字节流.
而NLS_LANG则用来告诉Oracle服务器端这个字节流的编码格式。
insert into test values(1,'好');

1. 当NLS_LANG所指定的编码和数据库编码一致时，这些字节数据部做任何转换，直接存入数据库。

2. 当NLS_LANG所指定的编码和数据库编码不一致时，则oralce在服务器端先用收到的数据按NLS_LANG所指定的编码进行解析，得到原始数据，
   然后再按数据库编码存储原始数据。
  
3. 如果NLS_LANG指定的编码格式和OS系统的编码方式不一致，将出现张冠李戴的现象导致乱码。

实验一

1. 系统编码 ZHS16GBK (CHCP 命令可以看系统默认编码,936表示ZHS16GBK)  

      C:\Documents and Settings\Administrator>chcp
     活动的代码页: 936

2. Database编码 AL32UTF8;

3. SQLPLUS客户端设置. set NLS_LANG=American_America.ZHS16GBK

下面显示了数据库和session的NLS 参数.
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                                             AL32UTF8
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                                            11.2.0.1.0

SQL> select * from nls_session_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_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ------------------------------
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

17 rows selected.

[D:\app\Administrator\product\11.2.0\dbhome_1]set NLS_LANG=American_America.AL32UTF8

[D:\app\Administrator\product\11.2.0\dbhome_1]sqlplus /@test as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 11 21:16:47 2013

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

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

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(10)

SQL> insert into test values(1,'好');
ERROR:
ORA-01756: quoted string not properly terminated

这里居然直接报语法错误，大家可能会认为是单引号字符不对导致第二个引号没有认出来，其实不是这个原因.
汉字"好" 通过系统的ZHS16GBK编码后是两个字节"BAC3"，而此时NLS_lang 指定的编码UTF8和服务器是一致的。

汉字"好"各编码如下 查询地址 http://bianma.supfree.net/
GBK编码     unicode编码        大五码(Big5)     区位码(GB2312)     utf8编码     10位unicode
BAC3         597D                   A66E             2635                   %E5%A5%BD       22909

当数据库收到这条SQL后没有对汉字"好"的编码进行转换，收到的东西可以理解成下面的形式。
insert into test values(1,'BAC3')

当数据库开始解析语句的时候，用UTF-8去解析参数(之所以这里会用UTF8解析是因为到数据库端的SQL如果含有直接字符，那么字符都应该是已经转码完成的，而这就是NLS_LANG应该保证的)，
当碰到第一个16进制数据"27"时,它知道这个单引号表示字符串开始，并不是数据,
然后往下找到16进制数据"BA",在UTF8编码规则里，第一个字节最高位连续1的个数表示这个UTF8编码的字节总数。
BA的二进制"10111010",高位只有一个1，那么表示本字节就是一个完整的UTF8编码,
C3的二进制"11000011",高位有两个连续1，表示后面连续的两个字符是一个完整的UTF8编码
于是11000011 (C3 ) + 00100111 (27) 被当成了一个完整的UTF8编码，最后解析器发现没有字符结束的单引号，于是报错ORA-01756: quoted string not properly terminated

下面是16进制对应的二进制码。

27                BA             C3                27
00100111   10111010   11000011      00100111

如果以上推断正确，那么我们在后面再加上一个单引号 就应该是可以保存的。 SQL如下,虽然很怪异，但是确实是成功保存了。

SQL> insert into test values(1,'好'');
1 row created.
SQL> select * from test;

        ID NAME
---------- ----------
         1 好'

同理我们也应该可以找到一些汉字，经过ZHS16GBK编码后，每个字节的高位都只有一个连续的1，然后根据UTF8规则，那么单个字节就是一个UTF8编码，这样就不会报错了。

汉字 "撼"各编码如下 查询地址 http://bianma.supfree.net/

GBK编码    unicode编码   大五码(Big5)    区位码(GB2312)   utf8编码            10位unicode
BAB3          64BC            BED9               2619              %E6%92%BC     25788

BAB3对应的二进制 10111010+10110011,经过尝试，插入成功。

BA         B3
10111010   10110011

SQL> insert into test values(1,'撼')
1 row created.

下面我们把数据和dump显示出来,可以看见"撼"对应的编码原封不动的存储了.

SQL> select name,dump(name,1016) as dump from test;

NAME       DUMP
---------- --------------------------------------------------
好'         Typ=1 Len=3 CharacterSet=AL32UTF8: ba,c3,27
撼          Typ=1 Len=2 CharacterSet=AL32UTF8: ba,b3
          
我们都知道汉字用UTF8编码是3个字节，而这里只有两个字节，
显然是不对的,可是为什么我们看到的数据是正确的呢？原因如下.

数据库在返回数据的时候，会先比对数据库的存储格式和客户端环境变量NLS_LANG指定的格式，发现两边是一样的UTF8，所以不进行任何转换，
数据原封不动的传回来。收到字符后进行解码和显示到屏幕上，这两个过程是由OS系来做的，所以OS按照它的编码ZHS16GBK解码收到的数据，
刚刚好还原出了最原始的数据。其实这是一种负负得正的巧合，另外开一个Session，把NLS_LANG的编码格式设置为ZHS16GBK(只要是非UTF8都行）
查询数据的时候就会看到乱码.

C:\Documents and Settings\Administrator>set NLS_LANG=American_America.ZHS16GBK

C:\Documents and Settings\Administrator>sqlplus /@test as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 11 23:50:36 2013

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

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

SQL> select * from test;

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

 

实验二

1. 系统编码 ZHS16GBK (CHCP 命令可以看系统默认编码,936表示ZHS16GBK)  C:\Documents and Settings\Administrator>chcp  活动的代码页: 936
  
2. Database编码 AL32UTF8;

3. SQLPLUS客户端设置. set NLS_LANG=American_America.WE8ISO8859P1

数据库和session的NLS参数同上

[D:\app\Administrator\product\11.2.0\dbhome_1]set NLS_LANG=American_America.WE8ISO8859P1

[D:\app\Administrator\product\11.2.0\dbhome_1]sqlplus /@test as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 12 00:02:49 2013

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

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

SQL> select * from test;

        ID NAME                   
---------- ----------
         1 靠
         1 靠

前一个实验的数据在当NLS_LANG编码不是UTF8是再次显示为乱码，为了不影响本次实验，我们新建一个表test2如下.

SQL> create table test2 (id number, des varchar2(10));

Table created.

SQL> insert into test2 values (1,'宋春风');        ---试图插入一行数据，报字段DES长度为10不够，插入的数据要12.
insert into test2 values (1,'宋春风')
                            *
ERROR at line 1:
ORA-12899: value too large for column "SYS"."TEST2"."DES" (actual: 12, maximum:
10)

先不管上面的错误，继续执行下面的SQL.

SQL> select length('宋春风'),lengthb('宋春风') ,length('宋'),lengthb('宋') from dual;

LENGTH('宋春非') LENGTHB('宋春非') LENGTH('宋') LENGTHB('宋')
---------------- ----------------- ------------ -------------
               6                12            2             4

我们发现"宋春风"的byte长度为12，单个汉字的byte长度为4,字符长度居然为2,真是怪异。

汉字"宋"的ZHS16GBK编码为"CBCE",而当前NLS_LANG指定的编码格式为WE8ISO8859P1，这是个单字节字符编码。
宋
GBK编码     unicode编码     大五码(Big5)     区位码(GB2312)     utf8编码     10位unicode
CBCE        5B8B             A7BA             4346              %E5%AE%8B     23435

所以"CBCE"传给Oracle服务端的时候，一个字节被当成一个字符，因此ZHS16GBK编码的"CBCE"就被服务器误认为是WE8ISO8859P1编码"CB","CE"对应的两个字符,然后返回的汉字"宋"的字符长度就是2.
如果是统计字符长度比如length函数，那么服务端收到的时候没必要转换成UTF8，因为数据库存的是UTF8编码的字节，当统计字符数目的时候，还是要用UTF8解码成字符，转换是多此一举而已。

WE8ISO8859P1编码里
"CB"对应 Ë ,后者在UTF8里的编码为"C38B"
"CE"对应 Î ,后者在UTF8里的编码为"C38E" 查编码可以用网页 http://bianma.911cha.com/

所以最后存储的是4个字节 C3 8B C3 8E,这也就是lengthb返回4的原因了。下面我们来仔细看看。

SQL> insert into test2 values (1,'宋');

1 row created.

SQL> select dump(des,1016) from test2;

DUMP(DES,1016)
-------------------------------------------------
Typ=1 Len=4 CharacterSet=AL32UTF8: c3,8b,c3,8e

结论：只有当NLS_LANG所指定的编码格式与OS系统的编码格式一致时,SQLPLUS 插入的数据（尤其是汉字）的编码才是正确的，才能保证数据库里数据的字节码与数据库编码对应.

SQLPLUS 乱码问题的更本解决办法就是设置正确的NLS_LANG.