Oracle LIMIT method.

Preamble

In Oracle PL/SQL, the LIMIT method is a function that returns the maximum number of elements that a collection can have. If the collection does not have the maximum number of elements, LIMIT returns NULL. Only Varray has the maximum size.

Syntax of the LIMIT collection method in Oracle PL/SQL

collection_name.LIMIT;

Parameters or arguments

• collection_name – the name of the collection.
• LIMIT – maximum number of elements that the collection can have.

Consider an example to understand how to use the LIMIT collection method in Oracle PL/SQL.

An example which outputs LIMIT and COUNT values for Associative Arrays with four elements, Varray with two elements and Nested Tables with three elements.

DECLARE
TYPE aa_type IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
aa aa_type; -- associative array

TYPE va_type IS VARRAY(4) OF INTEGER;
va va_type := va_type(2,4); -- varray

TYPE nt_type IS TABLE OF INTEGER;
nt nt_type := nt_type(1,3,5); -- nested table

BEGIN
aa(1):=3; aa(2):=6; aa(3):=9; aa(4):= 12;
DBMS_OUTPUT.PUT('aa.COUNT = '); print(aa.COUNT);
DBMS_OUTPUT.PUT('aa.LIMIT = '); print(aa.LIMIT);

DBMS_OUTPUT.PUT('va.COUNT = '); print(va.COUNT);
DBMS_OUTPUT.PUT('va.LIMIT = '); print(va.LIMIT);

DBMS_OUTPUT.PUT('nt.COUNT = '); print(nt.COUNT);
DBMS_OUTPUT.PUT('nt.LIMIT = '); print(nt.LIMIT);
END;

Result:
aa.COUNT = 4
aa.LIMIT = NULL
va.COUNT = 2
va.LIMIT = 4
nt.COUNT = 3
nt.LIMIT = NULL

PL/SQL tutorial: PL/SQL Collection Method LIMIT in Oracle Database

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.