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.

Syntax

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.

Examples

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:

ORDER_ID  ITEMS(PRODUCT_TYPE(PRODUCT_NAME, QUANTITY, PRICE), PRODUCT_TYPE(PRODUCT_NAME, QUANTITY, PRICE))
--------- ---------------------------------------------------
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 OR REPLACE TYPE phones AS TABLE OF VARCHAR2(20);

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'));

COMMIT;

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 c.name, p.COLUMN_VALUE as phone_number
FROM customers c, TABLE(c.phone_numbers) p;

The query result is as follows:

NAME          PHONE_NUMBER
------------- --------------------
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.

Conclusion

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.