Tag Archives: 32k strings

strings larger than 4000 in 12c

Back in Oracle 7, the maximum length for VARCHAR2 was 2000. In 11gR2, it is still 4000 for varchar2/char columns and for literals. Any attempt to use something larger will produce an infamous ora-910, ora-1704 or ora-1489 error.


SQL> create table t(x varchar2(5000));
create table t(x varchar2(5000))
                           *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> select 'x-
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-
... 100 times 
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-
' from dual;

select 'x-
       *
ERROR at line 1:
ORA-01704: string literal too long

SQL> select 'x'||
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
... 100 times 
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
'x' from dual;

select 'x'||
       *
ERROR at line 1:
ORA-01489: result of string concatenation is too long

In 12c it is possible to extends the varchar2 to 32k.

This is not the default and it is controlled by max_string_size


SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade quiet
ORACLE instance started.
Database mounted.
Database opened.
SQL> alter system set max_string_size=extended;
System altered.
SQL> @?/rdbms/admin/utl32k
SQL> shu immediate
Database closed.
Database dismounted.
SQL> startup quiet
ORACLE instance started.
Database mounted.
Database opened.

Now we can create, insert and select longer strings.


SQL> create table t(x varchar2(6000));
Table created.
SQL> desc t
 Name                  Null?    Type
 --------------------- -------- ----------------
 X                              VARCHAR2(6000)
SQL> insert into t values ('-
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-
... 100 times 
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
1 row created.
SQL> insert into t values (
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
... 100 times 
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
1 row created.

Oracle 8i came with a bunch of issue (cannot index a varchar2(4000) column) because the datatype was really increased to 4000. In 12c, however, it is silently using clob.


SQL> select TABLE_NAME,COLUMN_NAME,SEGMENT_NAME from user_lobs where table_name='T';

TABLE_NAME COLUMN_NAM SEGMENT_NAME
---------- ---------- ------------------------------
T          X          SYS_LOB0000022083C00001$$

It also works for NVARCHAR2 and RAW (who wasn’t increased to 4000). But not for CHAR.

This is a smart move to provide larger text fields to developers. On the other hand, why limit it to 32k if it is a clob? VARCHAR(MAX) in SQL Server does not have such a limitation.


SQL> create table t(x varchar2(32768));
create table t(x varchar2(32768))
                           *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> select 'x'||
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
... 999 times 
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
'x' from dual;
select
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long