LIMIT Method in PL/SQL – Varray Size and Extension Guide

The LIMIT Method in PL/SQL is a built-in function that returns the maximum number of elements a varray can hold. It is applicable only to varrays, and not to nested tables or associative arrays.

When working with collections, the LIMIT method plays a crucial role in ensuring you don’t exceed the varray’s predefined size during operations like EXTEND.

What is the LIMIT Method in PL/SQL?

The LIMIT Method in PL/SQL returns the maximum number of elements allowed in a varray (variable-size array).

Returns a PLS_INTEGER
Not applicable to nested tables or associative arrays
Helps prevent run-time exceptions when extending collections

Syntax of LIMIT Method in PL/SQL

collection_name.LIMIT

Returns:

An integer representing the maximum number of elements allowed
NULL if used with nested tables or associative arrays

Example: Using LIMIT Method in PL/SQL with a Varray

DECLARE
   -- Define a varray type with a limit of 5 elements
   TYPE number_varray IS VARRAY(5) OF INTEGER;
   number_list number_varray := number_varray(1,2,3);

   -- Local procedure to print elements
   PROCEDURE print_list(list_in number_varray) IS
   BEGIN
      FOR i IN list_in.FIRST..list_in.COUNT LOOP
         DBMS_OUTPUT.PUT_LINE('List Index ['||i||'] List Value ['||list_in(i)||']');
      END LOOP;
   END print_list;
BEGIN
   -- Display initial elements
   DBMS_OUTPUT.PUT_LINE('Varray after initialization');
   DBMS_OUTPUT.PUT_LINE('–––––––––––––');
   print_list(number_list);

   -- Extend the varray to its limit
   number_list.EXTEND(number_list.LIMIT - number_list.LAST);

   -- Display elements after extension
   DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Varray after extension');
   DBMS_OUTPUT.PUT_LINE('–––––––––––––');
   print_list(number_list);
END;
/

Output:

Varray after initialization
–––––––––––––
List Index [1] List Value [1]
List Index [2] List Value [2]
List Index [3] List Value [3]

Varray after extension
–––––––––––––
List Index [1] List Value [1]
List Index [2] List Value [2]
List Index [3] List Value [3]
List Index [4] List Value []
List Index [5] List Value []

Explanation of the LIMIT Method Usage

The varray is declared with a LIMIT of 5.
Initially, it contains 3 elements.
We calculate the difference using:

number_list.LIMIT - number_list.LAST

The EXTEND method then fills the remaining slots.

Best Practices for Using LIMIT Method in PL/SQL

Tip	Description
Use `LIMIT` with `EXTEND`	Prevents exceeding varray capacity
Check `COUNT` or `LAST` before extending	Ensures accuracy
Avoid using `LIMIT` on nested tables or associative arrays	Will return `NULL`
Use `LIMIT` in exception-safe collection operations	Avoids `SUBSCRIPT_BEYOND_COUNT` errors

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.