Introduction to Oracle NESTED TABLE Data Type

The NESTED TABLE data type in Oracle is a type of nested table that can store another table within a table. It is an object type in Oracle database used to create nested table data structures. Nested table types are dynamic, variable-length table types that can be used as column types, variable types, parameter types, function return types, etc.


The syntax for creating a NESTED TABLE type is as follows:

CREATE TYPE type_name AS TABLE OF column_datatype;

where type_name is the name of the new type, and column_datatype is the data type of the column.

Use Cases

Nested table types can be used to store one-to-many relationships, such as multiple order items in an order, multiple course grades for a student, etc. Nested table types can also be used to store complex object data types, such as objects containing arrays, structures, etc.


Here are two examples of using nested table types.

Example 1

Create a nested table type product_list_type to store multiple order items in an order, where each order item includes product name, quantity, and price.

CREATE TYPE product_type AS OBJECT (
  product_name VARCHAR2(100),
  quantity NUMBER,
  price NUMBER

CREATE TYPE product_list_type AS TABLE OF product_type;

Then, product_list_type can be used as a column type to create a table with multiple order items:

CREATE TABLE order_items (
  order_id NUMBER,
  items product_list_type

INSERT INTO order_items VALUES (1, product_list_type(product_type('Product A', 2, 100), product_type('Product B', 1, 200)));

Query the data in the order items table:

SELECT * FROM order_items;

The result is:

--------- ---------------------------------------------------
1         PRODUCT_TYPE('Product A', 2, 100), PRODUCT_TYPE('Product B', 1, 200)

Example 2

In this example, we will create a table with a NESTED TABLE column, insert some data, and then query the data using a SELECT statement.

First, we need to create a NESTED TABLE and a TABLE type to use the NESTED TABLE column in the table. Use the following statements to create these types:


CREATE TABLE customers (
  id NUMBER,
  name VARCHAR2(50),
  phone_numbers phones

Next, we can insert some data into the table, including a customer’s name and multiple phone numbers. Use the following statements to insert data:

INSERT INTO customers (id, name, phone_numbers)
VALUES (1, 'John Smith', phones('111-111-1111', '222-222-2222', '333-333-3333'));

INSERT INTO customers (id, name, phone_numbers)
VALUES (2, 'Jane Doe', phones('444-444-4444', '555-555-5555'));


Now, we can query the data in the table using a SELECT statement. Use the following statement to query the names and phone numbers of all customers in the table:

SELECT, p.COLUMN_VALUE as phone_number
FROM customers c, TABLE(c.phone_numbers) p;

The query result is as follows:

------------- --------------------
John Smith    111-111-1111
John Smith    222-222-2222
John Smith    333-333-3333
Jane Doe      444-444-4444
Jane Doe      555-555-5555

From the query results, it can be seen that NESTED TABLE columns can be used in a table and data can be queried using the TABLE function.


NESTED TABLE is a special data type provided by Oracle that can be used to store and query multiple values in a table. With NESTED TABLE, it is convenient to store and query nested table data, and use this data in PL/SQL code. NESTED TABLE can be used as a table column, as well as for storing procedure and function parameters and return values. Although NESTED TABLE has some limitations, it is still a very useful data type that can be used in various scenarios.