ORA-12899及UTF8字符集相关

一般设计数据库的时候，我们都考虑一个汉字占用两个字节。所以设计数据库的时候，如果认为某字段最长要存四个汉字，该字段都会定义为varchar2(8)。

SQL> create table t1 (col1 varchar2(8));
Table created.
但是测试插入三个汉字的时候就报错了。
SQL> insert into t1 values('一二三');
insert into t1 values('一二三')
*
ERROR at line 1:
ORA-12899: value too large for column T1.COL1 (actual: 9, maximum:8)

检查相关参数及环境变量。
SQL> select * from nls_database_parameters where parameter like 'NLS%CHARACTERSET';
PARAMETER                 VALUE
------------------------- --------------------
NLS_CHARACTERSET          UTF8
NLS_NCHAR_CHARACTERSET    UTF8
客户端NLS_LANG=AMERICAN_AMERICA.ZHS16GBK。

原来数据库使用的是UTF8字符集，难怪一个汉字占用3个字节。这样一来原先按一个汉字占两个字节设计的数据库，应用的时候很多字段都会因长度不够，出现ORA-12899错误。
不能把数据库的字符集改成ZHS16GBK。因为系统要求不仅能支持中文，还要能支持其他亚洲字符。这样CHARACTER SET就只能设这成UTF8或AL32UTF8，这两种字符集每个汉字占用的字节数分别是3和4，都不是2。
更改所有CHAR/VARCHAR字段的长度也不现实。整套系统是从其他公司买的产品，内含上千张表，逐个去修改字段不太可能。

查看参数NLS_LENGTH_SEMANTICS。
Oracl文档中的说明：
Syntax: NLS_LENGTH_SEMANTICS = string
Range of values: BYTE | CHAR
NLS_LENGTH_SEMANTICS enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected.
NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. You may be required to use byte semantics in order to maintain compatibility with existing applications.
NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM. The data dictionary always uses byte semantics.

查看数据库中该参数的设置。
SQL> select * from nls_database_parameters where parameter like 'NLS%SEMANTICS';
PARAMETER                 VALUE
------------------------- --------------------
NLS_LENGTH_SEMANTICS      BYTE
SQL> show parameter nls_length
NAME                                 TYPE       VALUE
------------------------------------ ---------- -------
nls_length_semantics                 string     BYTE

把该参数改成CHAR
SQL>alter system set NLS_LENGTH_SEMANTICS=BYTE scope=BOTH;
修改后查看参数。
SQL> show parameter nls_length
NAME TYPE VALUE
------------------------------------ ---------- -------
nls_length_semantics string CHAR

从新执行insert操作。
SQL> insert into t1 values('一二三');
insert into t1 values('一二三')
*
ERROR at line 1:
ORA-12899: value too large for column T1.COL1 (actual: 9, maximum:8)
还是出错!
新创建一张表，再测试。
SQL> create table t2 (col1 varchar2(8));
表已创建。
SQL> insert into t2 values('一二三')
已创建 1 行。
SQL> select * from t2;
COL1
----------------
一二三
OK了

再查看t1和t2的表结构，可以发现一些差别。
SQL> desc t1
名称                  是否为空? 类型
-------------------- -------- -------------------------
COL1                          VARCHAR2(8)
SQL> desc t2
名称                  是否为空? 类型
-------------------- -------- -------------------------
COL1                          VARCHAR2(8 CHAR)


参数NLS_LENGTH_SEMANTICS改成CHAR以后，t2.col1列可以存储8个汉字，英文字符也只能存储8个。
SQL> insert into t2 values('一二三四五六七八');
已创建 1 行。
SQL> insert into t2 values('abcdefgh');
已创建 1 行。
SQL> insert into t2 values('abcdefghi');
insert into t2 values('abcdefghi')
*
ERROR 位于第 1 行:
ORA-12899: value too large for column T2.COL1 (actual: 9, maximum:8)

本文转自:http://wuhuizhong.iteye.com/blog/740553