oracle的varchar2截断,ORACLE 12C varchar2支持32k长度字符串

在Oracle的以前版本中如果要存储超过4000byte的字符串需要使用clob字段,而lob本身操作就麻烦,而且效率不高。从12C开始Oracle提供了 VARCHAR2, NVARCHAR2, and RAW支持32k长度在字符串,大大提高了Oracle程序在处理4000到32k的字符串的处理效率.

数据库版本

SQL> select * from v$version;

BANNER CON_ID

-------------------------------------------------------------------------------- ----------

Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit 0

PL/SQL Release 12.1.0.0.2 0

CORE 12.1.0.0.2 0

TNS for Linux: Version 12.1.0.0.2 0

NLSRTL Version 12.1.0.0.2 0

max_sql_string_size参数

SQL> show parameter max_sql_string_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

max_sql_string_size string LEGACY

max_sql_string_size:controls maximum size of VARCHAR2, NVARCHAR2, and RAW types in SQL

max_sql_string_size, must be from among EXTENDED, LEGACY

创建测试表

SQL> create table t_xifenfei(id number,name varchar2(4001));

create table t_xifenfei(id number,name varchar2(4001))

*

ERROR at line 1:

ORA-00910: specified length too long for its datatype

SQL> create table t_xifenfei(id number,name varchar2(4000));

Table created.

SQL> insert into t_xifenfei values(1,rpad('www.xifenfei.com',4000,0));

1 row created.

SQL> insert into t_xifenfei values(2,lpad('www.xifenfei.com',4009,0));

1 row created.

SQL> commit;

Commit complete.

SQL> select id,length(name) from t_xifenfei;

ID LENGTH(NAME)

---------- ------------

1 4000

2 4000

SQL> select id,substr(name,-10,10) from t_xifenfei;

ID SUBSTR(NAME,-10,10)

---------- ----------------------------------------

1 0000000000

2 fenfei.com

SQL> select id,substr(name,3990) from t_xifenfei;

ID SUBSTR(NAME,3990)

---------- --------------------------------------------

1 00000000000

2 ifenfei.com

测试说明几点：

1.默认情况下varchar2长度不能超过4000

2.插入varchar2超过4000的字段(列长度为4000),自动被截断

修改max_sql_string_size参数

SQL> alter system set max_sql_string_size='EXTENDED';

alter system set max_sql_string_size='EXTENDED'

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-02096: specified initialization parameter is not modifiable with this option

SQL> alter system set max_sql_string_size='EXTENDED' scope=spfile;

System altered.

执行utl32k.sql脚本

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 313159680 bytes

Fixed Size 2259912 bytes

Variable Size 243270712 bytes

Database Buffers 62914560 bytes

Redo Buffers 4714496 bytes

Database mounted.

Database opened.

SQL> @?/rdbms/admin/utl32k.sql

--包含编译无效对象

QL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 313159680 bytes

Fixed Size 2259912 bytes

Variable Size 251659320 bytes

Database Buffers 54525952 bytes

Redo Buffers 4714496 bytes

Database mounted.

Database opened.

SQL> show parameter max_sql_string_size;

NAME TYPE VALUE

------------------------------------ ----------- ----------------

max_sql_string_size string EXTENDED

测试varchar2(32767)

SQL> conn xff/xifenfei

Connected.

SQL> create table t_xifenfei_1(id number,name varchar2(32768));

create table t_xifenfei_1(id number,name varchar2(32768))

*

ERROR at line 1:

ORA-00910: specified length too long for its datatype

SQL> create table t_xifenfei_extend(id number,name varchar2(32767));

Table created.

SQL> insert into t_xifenfei_extend values(3,lpad('www.xifenfei.com',32767,0));

1 row created.

SQL> commit;

Commit complete.

SQL> select id,substr(name,-10,10) from t_xifenfei_extend;

ID SUBSTR(NAME,-10,10)

---------- ----------------------------------------

3 fenfei.com

SQL> select id,substr(name,32760) from t_xifenfei_extend;

ID SUBSTR(NAME,32760)

---------- --------------------------------

3 nfei.com

SQL> select id,length(name) from t_xifenfei_extend;

ID LENGTH(NAME)

---------- ------------

3 32767

Oracle 12C支持字符串32K处理过程

1.修改max_sql_string_size=’EXTENDED’

2.重启数据库至upgrade状态

3.执行@?/rdbms/admin/utl32k.sql

4.重启数据库至正常open状态