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.
