Oracle PL/SQL - EXTEND Collection Method

Introduction

EXTEND adds elements to the end of a varray or nested table.

The collection can be empty, but not null.

The EXTEND method has these forms:

EXTEND appends one null element to the collection.
EXTEND(n) appends n null elements to the collection.
EXTEND(n,i) appends n copies of the i-th element to the collection.

EXTEND(n,i) can use for a collection whose elements have the NOT NULL constraint.

If DELETE deletes an element but keeps a placeholder for it, then EXTEND considers the element to exist.

The following table shows how to use EXTEND Method with Nested Table.

Demo

SQL>
SQL>--  www.  j  av  a  2s  .c o m
SQL> CREATE OR REPLACE TYPE nt_type IS TABLE OF NUMBER;
  2  /

Type created.

SQL> CREATE OR REPLACE PROCEDURE print_nt (nt nt_type) IS
  2    i  NUMBER;
  3  BEGIN
  4    i := nt.FIRST;
  5
  6    IF i IS NULL THEN
  7      DBMS_OUTPUT.PUT_LINE('nt is empty');
  8    ELSE
  9      WHILE i IS NOT NULL LOOP
 10        DBMS_OUTPUT.PUT('nt.(' || i || ') = '); print(nt(i));
 11        i := nt.NEXT(i);
 12      END LOOP;
 13    END IF;
 14  END print_nt;
 15  /

Procedure created.


SQL>
SQL> DECLARE
  2    nt nt_type := nt_type(11, 22, 33);
  3  BEGIN
  4    print_nt(nt);
  5
  6    nt.EXTEND(2,1);  -- Append two copies of first element
  7    print_nt(nt);
  8
  9    nt.DELETE(5);    -- Delete fifth element
 10    print_nt(nt);
 11
 12    nt.EXTEND;       -- Append one null element
 13    print_nt(nt);
 14  END;
 15  /
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 11
nt.(5) = 11
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 11
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 11
nt.(6) = NULL

PL/SQL procedure successfully completed.

SQL>