PL/SQL maximum size of VARRAY

Question

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

• William Robertson: 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?

Answer 1

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.

• Vagiz Duseev: Do you think PL/SQL has special constant for that value?
• Husqvik: I'm not aware about any specific keyword for it. Some built-in packages defines this value regular way using my_constant CONSTANT NUMBER := 2147483647;