You can use the LIMIT function to return the maximum number of elements in a collection. If the maximum number of elements is not specified for the collection, the LIMIT method returns NULL.
The maximum number of elements can be specified only for a variable-size array (or varray).
Example:
obclient> DECLARE
TYPE asa_ty IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
asa asa_ty; -- Associative array
TYPE vaa_ty IS VARRAY(5) OF INTEGER;
vaa vaa_ty := vaa_ty(2,4,6); -- Varray
TYPE nested_ty IS TABLE OF INTEGER;
ntt nested_ty := nested_ty(1,3,5,7); -- Nested table
BEGIN
asa(1):=2; asa(2):=4; asa(3):=6; asa(4):= 8; asa(5):= 10;
DBMS_OUTPUT.PUT('asa.COUNT = ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(asa.COUNT), 'NULL'));
DBMS_OUTPUT.PUT('asa.LIMIT = ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(asa.LIMIT), 'NULL'));
DBMS_OUTPUT.PUT('vaa.COUNT = ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(vaa.COUNT), 'NULL'));
DBMS_OUTPUT.PUT('vaa.LIMIT = ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(vaa.LIMIT), 'NULL'));
DBMS_OUTPUT.PUT('ntt.COUNT = ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(ntt.COUNT), 'NULL'));
DBMS_OUTPUT.PUT('ntt.LIMIT = ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(ntt.LIMIT), 'NULL'));
END;
/
Query OK, 0 rows affected
asa.COUNT = 5
asa.LIMIT = NULL
vaa.COUNT = 3
vaa.LIMIT = 5
ntt.COUNT = 4
ntt.LIMIT = NULL
This example describes an associative array with five elements, a varray with three elements, and a nested table with four elements, and outputs the LIMIT and COUNT values.