Oracle varchar2 4000

关于oracle varchar2 官方文档的描述

VARCHAR2 Data Type

The VARCHAR2 data type specifies a variable-length character string. When you create a VARCHAR2 column, you supply the maximum number of bytes or characters of data that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the column's maximum length of the column. If you try to insert a value that exceeds the specified length, then Oracle returns an error.

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. You can use the BYTE qualifier, for example VARCHAR2(10 BYTE), to explicitly give the maximum length in bytes. If no explicit qualifier is included in a column or attribute definition when a database object with this column or attribute is created, then the length semantics are determined by the value of the NLS_LENGTH_SEMANTICS parameter of the session creating the object. Independently of the maximum length in characters, the length of VARCHAR2 data cannot exceed 4000 bytes. Oracle compares VARCHAR2 values using nonpadded comparison semantics.

测试环境：

SQL> select * from v$version t where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> select * from v$nls_parameters t where t.PARAMETER='NLS_LENGTH_SEMANTICS';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------
---------
NLS_LENGTH_SEMANTICS                                             BYTE

SQL>

创建测试表

create table test_varchar2_datatype(
char_1 varchar2(4000 byte),
char_2 varchar2(4000 char),
char_3 varchar2(4000),
char_21 varchar2(2000 byte),
char_22 varchar2(2000 char),
char_23 varchar2(2000)
);

测试过程username列都为汉字

SQL> select length(listagg(t.username) within group (order by t.userid)||'中国石化') from t_char_source t
  2  where rownum <148;

LENGTH(LISTAGG(T.USERNAME)WITHINGROUP(ORDERBYT.USERID)||'中国石化')
-------------------------------------------------------------------
                                                               2000

SQL> insert into test_varchar2_datatype(char_1,
  2                                     char_2,
  3                                     char_3,
  4                                     char_21,
  5                                     char_22,
  6                                     char_23
  7                                     )
  8  select
  9  listagg(t.username) within group (order by t.userid)||'中国石化',
 10  listagg(t.username) within group (order by t.userid)||'中国石化',
 11  listagg(t.username) within group (order by t.userid)||'中国石化',
 12  listagg(t.username) within group (order by t.userid)||'中国石化',
 13  listagg(t.username) within group (order by t.userid)||'中国石化',
 14  listagg(t.username) within group (order by t.userid)||'中国石化'
 15  from t_char_source t
 16  where rownum <148;
listagg(t.username) within group (order by t.userid)||'中国石化',
                                                     *
第 12 行出现错误:
ORA-12899: 列 "HR"."TEST_VARCHAR2_DATATYPE"."CHAR_21" 的值太大 (实际值: 3991, 最大值: 2000)


SQL> insert into test_varchar2_datatype(char_1,
  2                                     char_2,
  3                                     char_3,
  4                                     char_22,
  5                                     char_23
  6                                     )
  7  select
  8  listagg(t.username) within group (order by t.userid)||'中国石化',
  9  listagg(t.username) within group (order by t.userid)||'中国石化',
 10  listagg(t.username) within group (order by t.userid)||'中国石化',
 11  listagg(t.username) within group (order by t.userid)||'中国石化',
 12  listagg(t.username) within group (order by t.userid)||'中国石化'
 13  from t_char_source t
 14  where rownum <148;
listagg(t.username) within group (order by t.userid)||'中国石化'
                                                     *
第 12 行出现错误:
ORA-12899: 列 "HR"."TEST_VARCHAR2_DATATYPE"."CHAR_23" 的值太大 (实际值: 3991, 最大值: 2000)

SQL> insert into test_varchar2_datatype(char_1,
  2                                     char_2,
  3                                     char_3,
  4                                     char_22
  5                                     )
  6  select
  7  listagg(t.username) within group (order by t.userid)||'中国石化',
  8  listagg(t.username) within group (order by t.userid)||'中国石化',
  9  listagg(t.username) within group (order by t.userid)||'中国石化',
 10  listagg(t.username) within group (order by t.userid)||'中国石化'
 11  from t_char_source t
 12  where rownum <148;

已创建 1 行。

SQL> commit;
                                                               
SQL> select length(listagg(t.username) within group (order by t.userid)||'中国石化') from t_char_source t
  2  where rownum <150;
select length(listagg(t.username) within group (order by t.userid)||'中国石化') from t_char_source t
                                                                                               *
第 1 行出现错误:
ORA-01489: 字符串连接的结果过长


SQL>                                                               

结论：

1.varchar2的指定方式为：VARCHAR2(size [BYTE | CHAR]) ，如果没有指定byte或是char 由参数NLS_LENGTH_SEMANTICS确定计数的是byte还是char。

2.4000byte 是死结，无论指定的是byte 或 char 都无法超越4000byte，就是说占多个byte的字符比如汉字最多2000个，即便是在创建表的时候指定了4000 char,最后也只能装4000byte的量。Nvarchar2 同理。(LOB 字段 XMLagg 除外）

3.函数中如果超过4000个byte也会报ORA-01489错误。

4.函数wn_concat在官方文档没有介绍，在oracle 12c 中已经没有该函数了。oracle 11g and up 可以使用 listagg替代，listagg 在oracle 官方文档有描述。