Data Structures   «Prev  Next»

Four Oracle Data Constructs with a description of Nested Table

  1. Nested table: A construct for embedding a sub-table within a table (see the paragraph below for a detailed description)
  2. ADT: A method for grouping related items together
  3. DEREF: The command that displays the row that an OID points to
  4. OID: A persistent Oracle construct to identify a row
  5. CREATE TYPE: The command to create an object type definition

Nested Tables

In the database, a nested table is a column type that stores an unspecified number of rows in no particular order. 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. Using these indexes, you can access the individual rows of the nested table variable. The syntax is variable_name(index). The indexes and row order of a nested table might not remain stable as you store and retrieve the nested table from the database.
The amount of memory that a nested table variable occupies can increase or decrease dynamically, as you add or delete elements. An uninitialized nested table variable is a null collection. You must initialize it, either by making it empty or by assigning a non-NULL value to it.
Example 3-1 defines a local nested table type, declares a variable of that type (initializing it with a constructor), and defines a procedure that prints the nested table.
The example invokes the procedure three times: After initializing the variable, after changing the value of one element, and after using a constructor to the change the values of all elements. After the second constructor invocation, the nested table has only two elements. Referencing element 3 would raise error ORA-06533.

Example 3-1 Nested Table of Local Type

DECLARE
TYPE Roster IS TABLE OF VARCHAR2(15); -- nested table type
-- nested table variable initialized with constructor:
names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
PROCEDURE print_names (heading VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);
FOR i IN names.FIRST .. names.LAST LOOP -- For first to last element
DBMS_OUTPUT.PUT_LINE(names(i));
END LOOP;
DBMS_OUTPUT.PUT_LINE('---');
END;
BEGIN
print_names('Initial Values:');
names(3) := 'P Perez'; -- Change value of one element
print_names('Current Values:');
names := Roster('A Jansen', 'B Gupta'); -- Change entire table
print_names('Current Values:');
END;
/

Output displayed below

Result:
Initial Values:
D Caruso
J Hamil
D Piro
R Singh
---
Current Values:
D Caruso
J Hamil
P Perez
R Singh
---
Current Values:
A Jansen
B Gupta