You can use the LIMIT method 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.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
The maximum number of elements can be specified only for a variable-size array (or varray).
Here is an 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.