CODEWITHSUNDEEP
oracle-databaseoracle11goracle10g
gravetii
gravetii

Reputation: 9624

What is the limit for varchar2 in a Table of Type in Oracle?

I am looking for the limit of the varchar2 type as far as a Table of type is concerned in oracle. Note that I am not talking about a physical table (for which the limit for varchar2 is 4000) but the table of a type.

Thanks

Upvotes: 1

Views: 3372

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59455

In Oracle 12c the limit is 32'767 bytes. However, it must be enabled by Oracle parameter MAX_STRING_SIZE = EXTENDED. You should follow the procedure as given in Oracle docs: MAX_STRING_SIZE

Upvotes: 2

Jon Heller
Jon Heller

Reputation: 36817

It depends on the context. A table of VARCHAR2(32767) can be created anywhere. But 32767 can only be used in PL/SQL. The limit in SQL is still 4000.

SQL> --#1: SQL, 4000.
SQL> create or replace type varchar2_limit is table of varchar2(4000);
  2  /

Type created.

SQL> create table varchar2_limit_table1
  2  (
  3     a varchar2_limit
  4  ) nested table a store as my_nt;

Table created.

SQL> --#2: SQL, 4001.
SQL> drop table varchar2_limit_table1;

Table dropped.

SQL> create or replace type varchar2_limit is table of varchar2(4001);
  2  /

Type created.

SQL> create table varchar2_limit_table2
  2  (
  3     a varchar2_limit
  4  ) nested table a store as my_nt;
create table varchar2_limit_table2
*
ERROR at line 1:
ORA-02320: failure in creating storage table for nested table column A
ORA-00910: specified length too long for its datatype


SQL> --#3: PL/SQL, 32767.
SQL> declare
  2     type varchar2_limit is table of varchar2(32767);
  3  begin
  4     null;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> --#4: PL/SQL, 32768
SQL> declare
  2     type varchar2_limit is table of varchar2(32768);
  3  begin
  4     null;
  5  end;
  6  /
        type varchar2_limit is table of varchar2(32768);
                                                 *
ERROR at line 2:
ORA-06550: line 2, column 43:
PLS-00215: String length constraints must be in range (1 .. 32767)

Upvotes: 6

Related Questions