Oracle PL/SQL - PL SQL Data Type Nested Tables

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. Nested Tables

Introduction

Oracle nested table is a column type that stores an unspecified number of rows.

When you retrieve a nested table value from the database into a PL/SQL nested table variable, PL/SQL gives the rows consecutive indexes, starting at 1.

You can access the individual rows of the nested table variable via these indexes.

The syntax is variable_name(index).

An uninitialized nested table variable is a null collection.

The following code defines a local nested table type.

Demo

SQL>
SQL> DECLARE-- from  w w w. j a  v a 2s.  c  o  m
  2    TYPE StringList IS TABLE OF VARCHAR2(15); 
  3
  4    
  5
  6    names StringList := StringList('A', 'B', 'C', 'D');
  7
  8    PROCEDURE print_names (heading VARCHAR2) IS
  9    BEGIN
 10      DBMS_OUTPUT.PUT_LINE(heading);
 11
 12      FOR i IN names.FIRST .. names.LAST LOOP 
 13        DBMS_OUTPUT.PUT_LINE(names(i));
 14      END LOOP;
 15    END;
 16
 17  BEGIN
 18    print_names('Initial Values:');
 19
 20    names(3) := 'X'; 
 21    print_names('Current Values:');
 22
 23    names := StringList('Y', 'Z'); 
 24    print_names('Current Values:');
 25  END;
 26  /
Initial Values:
A
B
C
D
Current Values:
A
B
X
D
Current Values:
Y
Z

PL/SQL procedure successfully completed.

SQL>

Related Topics