PL/SQL maximum size of VARRAY

Vagiz Duseev picture Vagiz Duseev · Nov 10, 2015 · Viewed 9.4k times · Source

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)?

Answer

Husqvik picture Husqvik · Nov 10, 2015

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.