5

I'm trying to figure out the possible upper bound of VARRAY in PL/SQL.

We sure can define VARRAY type as

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) 
   OF element_type [NOT NULL];

Oracle documentation has this to say:

Each varray is stored as a single object, either inside the table of which it is a column (if the varray is less than 4KB) or outside the table but still in the same tablespace (if the varray is greater than 4KB). You must update or retrieve all elements of the varray at the same time, which is most appropriate when performing some operation on all the elements at once. But you might find it impractical to store and retrieve large numbers of elements this way.

But what is the upper bound of size_limit parameter? Is it equal to unsigned integer (4,294,967,295)?

Vagiz Duseev
  • 702
  • 1
  • 7
  • 12
  • 1
    Surely for any limit much greater than single digits, there is a diminishing benefit to declaring a limit at all. If the intention is to have one array element per US state, for example, it would be reasonable to declare a `varray(50) of varchar2(2)`. But _2 billion_? – William Robertson Sep 22 '16 at 13:51

1 Answers1

10

Within PL/SQL the limit is 2147483647. The same limit applies to schema varying array types.

DECLARE
    TYPE t IS VARRAY(2147483647) OF NUMBER;
BEGIN
    NULL;
END;

If you increase it it throws PLS-00325: non-integral numeric literal 2147483648 is inappropriate in this context.

Husqvik
  • 5,669
  • 1
  • 19
  • 29