CODEWITHSUNDEEP
oracle-databaseplsql
Richard Spencer
Richard Spencer

Reputation: 631

Oracle 12.1 Data types

According to the Oracle 12 documentation ( 1.1.6.4 ) on Oracle's web site you are supposed to be able to create a varchar2 column with more than 4000 characters. Has anyone manged to do this as all I get is :-

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table r1 ( c1 integer, c2 varchar2(32000));
create table r1 ( c1 integer, c2 varchar2(32000))
                                          *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

Upvotes: 1

Views: 576

Answers (1)

Alex Poole
Alex Poole

Reputation: 191580

The documentation also states:

Beginning with Oracle Database 12c, you can specify a maximum size of 32767 bytes for the VARCHAR2, NVARCHAR2, and RAW data types. You can control whether your database supports this new maximum size by setting the initialization parameter MAX_STRING_SIZE as follows:

  • If MAX_STRING_SIZE = STANDARD, then the size limits for releases prior to Oracle Database 12c apply: 4000 bytes for the VARCHAR2 and NVARCHAR2 data types, and 2000 bytes for the RAW data type. This is the default.

  • If MAX_STRING_SIZE = EXTENDED, then the size limit is 32767 bytes for the VARCHAR2, NVARCHAR2, and RAW data types.

So you need to set your MAX_STRING_SIZE initialisation parameter to make this work.

(I haven't had a chance to play with 12c yet, so I'm just going on what the docs say too at this point).

Upvotes: 3

Related Questions