February 27, 2021
Hot Topics:

Oracle Programming with PL/SQL Collections

  • By Michael Klaene
  • Send Email »
  • More Articles »

Nested Table

Nested Tables, like the Varray, can be stored in a relational table as well as function as a PL/SQL program variable. The syntax for declaring a Nested Table is similar to the syntax for declaring the traditional PL/SQL Table. Let's rework our earlier example using a Nested Table. First, you declare your type:

CREATE TYPE genres_tab IS TABLE OF book_genre.genre_name%TYPE;

The 'IS TABLE OF' syntax was also used when declaring a PL/SQL Table. However, this declaration omits the 'INDEX BY BINARY_INTEGER' clause required by the former type. Note that we have not specified the size of the collection. This is because Nested Tables, unlike the Varray, require no size specification. In other words, they are unbound. Here is a definition for the book_library database table, which now contains a Nested Table column:

CREATE TABLE book_library (
    library_id     NUMBER,
    name           VARCHAR2(30),
    book_genres_tab genres_tab)
    NESTED TABLE book_genres_tab STORE AS genres_table;

As stated earlier, a Varray's contents are stored in the same table as the other columns' data (unless the collection is exceedingly large, then Oracle stores it in a BLOB, but still within the same tablespace). With Nested Tables, a seperate database table will store the data. This table is specified following the 'STORE AS' clause. If a database table has more than one Nested Table type, the same storage table will store data for all the Nested Tables on that parent table. These storage tables contain a column called NESTED_TABLE_ID that allows the parent table to reference a row's nested table data.

--Insert a record into book_library, with a Nested Table of book genres.
INSERT INTO book_library (library_id, name, book_genres_tab)
  VALUES (book_library_seq.NEXTVAL,'Brand New Library', 

--Declare a nested table type
  updated_genres_tab genres_tab;
  updated_genres_tab := 

  --Update the existing record with a new genres Nested Table.
  UPDATE book_library 
    SET book_genres_tab = updated_genres_tab;


These examples show an insert and an update to the book_table and are similar to what you might see if you were working with a Varray. Both Nested Tables and Varrays allow you to use SQL to select individual elements from a collection. However, Nested Tables have an advantage over Varrays in that they allow for inserts, updates, and deletes on individual elements. The Varray type does not because Varray data is stored as one single, delimited piece of data within the database.

To operate on collection elements, use the TABLE command. The TABLE command operator informs Oracle that you want your operations to be directed at the collection, instead of its parent table.

--1.)Select all genres from library 'Brand New Library' that are like
SELECT column_value FROM TABLE(SELECT book_genres_tab
                               FROM book_library 
                               WHERE name = 'Brand New Library')
   WHERE column_value LIKE '%FICTION%';


--2.)Update entry 'MULTIMEDIA' to a new value.  Only possible with
     a nested table!!
UPDATE TABLE(SELECT book_genres_tab 
             FROM book_library 
             WHERE name = 'Brand New Library')
  SET column_value   = 'MUSIC AND FILM'
  WHERE column_value = 'MULTIMEDIA';

--3.)Select all book_genre_tab entries for this library.
SELECT column_value FROM TABLE(SELECT book_genres_tab
                               FROM book_library 
                               WHERE name = 'Brand New Library');


The first of the preceding three statements simply selects an individual element from the Nested Table, book_genres_tab, in our book_library database table. The second statement performs an update on an individual element, something possible only with Nested Tables. The last query shown selects all Nested Table elements from the parent table. This demonstrates an important feature of the TABLE operator. An ealier query we performed on a database column of type Varray returned a single comma-delimited list of values ('FICTION', 'NON-FICTION', 'HISTORY', 'BUSINESS AND FINANCE'). Using TABLE allows you to 'unnest' a collection and display its elements as you would a database table's results, top down.

Page 3 of 4

This article was originally published on July 12, 2004

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date