对oracle varchar2(..... char)的疑惑

sqysl

1、这和数据库的字符集有关系，楼主的字符集是SIMPLIFIED CHINESE_CHINA.ZHS16GBK，一个汉字在这个字符集中应该占用两个字节，因此，楼主的表中A字段最多只能存储4000/2=2000个汉字；
2、nls_length_semantics说明长度的默认意义，即length得出的值的默认单位，但在varchar2(4000 char)中，覆盖了该参数的默认值，即该字段最大可以存储4000个字符而不是字节，length()返回的也是字符数，而不是字节数；
3、just_dba的试验中就说明了这点，只是他的数据库中一个汉字占用了三个字节，所以最终在试验中只插入了1333个汉字:3*1333=3999；
4、下面是ORACLE官方资料的一段，大家可以参考：
Oracle® Database
SQL Language Reference
11g Release 1 (11.1)
B28286-02
September 2007

P44

You must specify a maximum length for a VARCHAR2 column. This maximum must be
at least 1 byte, although the actual string stored is permitted to be a zero-length string
(''). You can use the CHAR qualifier, for example VARCHAR2(10 CHAR), to give the
maximum length in characters instead of bytes. A character is technically a code point
of the database character set. CHAR and BYTE qualifiers override the setting of the
NLS_LENGTH_SEMANTICS parameter, which has a default of bytes. For performance
reasons, Oracle recommends that you use the NLS_LENGTH_SEMANTICS parameter to
set length semantics and that you use the BYTE and CHAR qualifiers only when
necessary to override the parameter. The maximum length of VARCHAR2 data is 4000
bytes. Oracle compares VARCHAR2 values using nonpadded comparison semantics.

Yong Huang

dingjun123 and sqysl are correct. I missed the point that "The maximum length of VARCHAR2 data is 4000 bytes". At the time you define varchar2(4000 char) or varchar2(3000 char), Oracle won't complain, because it doesn't know if your char will be more than 1 byte. When you actually insert data, the limit of 4000 bytes kicks in.

Yong Huang