insert into varchar2(8000)

在看12c的文档的时候发现varcahr2最大长度是4000 byte

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 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.

To ensure proper data conversion between databases with different character sets, you must ensure that VARCHAR2 data consists of well-formed strings. SeeOracle Database Globalization Support Guide for more information on character set support.

 

但是下面的SQL可以把8000长度的字符串insert到varchar2中。 为什么呢？

declare
    v1 varchar2(8000);
begin
    v1 := 'a';
    for i in 1..7900 loop
        v1 := v1||'a';
    end loop;
    --dbms_output.put_line(v1);
    insert into t values(v1);
end;

 

这是因为oracle 12c中通过设置MAX_STRING_SIZE to EXTENDED, 可以增加 VARCHAR2, NVARCHAR2, and RAW data types 的最大大小。 这个参数设置为STANDAND的时候，这些类型的大小是 4000 bytes for VARCHAR2 andNVARCHAR2, and 2000 bytes for RAW。 而设置成EXTENED之后这些类型的最大大小变成了 32767 byte。

另一个要注意的地方是这个是12c的新特性，而且在设置的时候并不是说简单的 ALER SYSTEM SET MAX_STRING_SIZE就可以了。根据不同的情况要有不同的操作步骤。