Oracle PL/SQL Collections - Create Nested Table in the Database

Oracle PL/SQL Collections - Create Nested Table in the Database

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PLSQL
  • 2 mins read

In Oracle, a nested table can be stored as a database column. This means that the entire nested table is contained in one row of the database table, and each row of the database can contain a different nested table. To store a nested table in the database, you must use the CREATE TYPE statement to creates the nested table type, rather than a type statement in a PL/SQL block.

By using CREATE TYPE, the type is stored in the data dictionary and is thus available for use as a column type. The following example illustrates how to create a nested table as a database column.

Oracle PL/SQL - Creating Nested Table in the Database

CREATE TYPE BookObj AS OBJECT (
title VARCHAR2(40),
author VARCHAR2(40),
catalog_number NUMBER(4)
);

CREATE TYPE BookList AS TABLE OF BookObj;

CREATE TABLE course_material (
department CHAR(3),
course NUMBER(3),
required_reading BookList )
NESTED TABLE required_reading STORE AS required_tab;

There are several things to note about the above listing and creating nested tables in the database:

  • The table type is designed with the CREATE TYPE statement so it can be stored in the data dictionary.
  • The table type is used in the table definition, just like a column object.
  • For each nested table in a given database table, the  NESTED TABLE clause is required. This clause indicates the name of the store table.

A store table is a system-generated table that is used to store the actual data in the nested table. This data is not stored inline with the rest of the table columns; it is stored separately.

The required_reading column will store a REF into the required_tab table, where the list of books will be stored. For each row of course_material, required_reading contains a REF to the corresponding rows in required_tab.

NOTE

The store table (required_tab in the above example) can exist in another schema and can have different storage parameters from the main table. The store table can be described, and exists in user_tables, but can not be accessed directly.

If you attempt to query or modify the store table directly, you will get the Oracle error "ORA-22812: cannot reference nested table column's storage table". The contents of the store table are manipulated through SQL on the main table.

See also: