EXTEND Method in PL/SQL – Syntax, Parameters, and Example

The EXTEND Method in PL/SQL is used to dynamically grow collections such as nested tables and varrays. It allocates space for additional elements at runtime, making your PL/SQL programs more flexible and memory-efficient.

This guide explains how the EXTEND method works, its overloaded forms, and real examples to help you use it effectively.

What is the EXTEND Method in PL/SQL?

The EXTEND method is a procedure used to increase the number of elements in a collection. It supports multiple overloaded forms, allowing you to either:

Add empty elements,
Add multiple elements at once,
Or even clone existing elements.

This method is especially useful when working with dynamic arrays or buffer-style logic in Oracle PL/SQL.

EXTEND Method Syntax in PL/SQL

collection_name.EXTEND               -- Adds one empty element
collection_name.EXTEND(n)           -- Adds 'n' empty elements
collection_name.EXTEND(n, i)        -- Adds 'n' elements by copying element at index 'i'

Example: EXTEND with Single and Double Parameters

DECLARE
   -- Define a nested table of integers
   TYPE number_table IS TABLE OF INTEGER;
   number_list number_table := number_table(1, 2);

   -- Procedure to print collection contents
   PROCEDURE print_list(list_in number_table) IS
   BEGIN
      FOR i IN list_in.FIRST .. list_in.LAST LOOP
         IF list_in.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE('List [' || NVL(TO_CHAR(list_in(i)), 'NULL') || ']');
         END IF;
      END LOOP;
   END;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Nested table before extension');
   DBMS_OUTPUT.PUT_LINE('------------------------------');
   print_list(number_list);

   -- Add two empty elements
   number_list.EXTEND(2);

   -- Add three elements, copying value from index 2
   number_list.EXTEND(3, 2);

   DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Nested table after extension');
   DBMS_OUTPUT.PUT_LINE('------------------------------');
   print_list(number_list);
END;
/

Output:

Nested table before extension
List [1]
List [2]

Nested table after extension
List [1]
List [2]
List [NULL]
List [NULL]
List [2]
List [2]
List [2]

EXTEND Method Overloads

Syntax	Description
`EXTEND`	Adds one null (uninitialized) element
`EXTEND(n)`	Adds `n` null elements
`EXTEND(n, i)`	Adds `n` elements and copies from index `i`

EXTEND will raise a SUBSCRIPT_BEYOND_COUNT exception if you try to copy from an index that does not exist.

EXTEND Limitations with VARRAYs

While EXTEND works on nested tables and associative arrays, it respects the size limit of VARRAYs.

Trying to EXTEND beyond a VARRAY’s defined maximum size will raise a SUBSCRIPT_BEYOND_LIMIT exception.
Always use COUNT to check how many elements exist before calling EXTEND on a VARRAY.

Best Practices for Using EXTEND in PL/SQL

Always initialize the collection before using EXTEND.
Use COUNT, LIMIT, or EXISTS to avoid exceptions.
Use EXTEND(n, i) only when you’re sure i exists in the collection.
Avoid blindly extending beyond a VARRAY’s capacity.

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