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