What is a Nested Table?

A Nested Table is an Oracle collection type that stores multiple values of the same data type in a single column or PL/SQL variable. Unlike VARRAYs, a nested table has no fixed size and can grow dynamically.

It is useful when one record needs to store multiple related values, such as a student having multiple subjects or an employee having multiple phone numbers.

Features of Nested Tables

• Stores multiple elements of the same data type.
• Has no maximum size (dynamic).
• Elements can be inserted, updated, and deleted.
• Can be stored in a database table or used in PL/SQL.
• Supports collection methods such as COUNT, EXTEND, DELETE, FIRST, LAST, and EXISTS.

Syntax

1. Create a Nested Table Type

CREATE TYPE type_name AS TABLE OF datatype;
/

2. Declare a Variable

variable_name type_name;

Example 1: Nested Table in PL/SQL

DECLARE
    TYPE marks_tab IS TABLE OF NUMBER;

    marks marks_tab := marks_tab(80, 85, 90, 95);

BEGIN
    FOR i IN 1 .. marks.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Mark = ' || marks(i));
    END LOOP;
END;
/

Output

Mark = 80
Mark = 85
Mark = 90
Mark = 95

Example 2: Adding Elements Using EXTEND

DECLARE
    TYPE names_tab IS TABLE OF VARCHAR2(30);

    names names_tab := names_tab();

BEGIN
    names.EXTEND;
    names(1) := 'Rahul';

    names.EXTEND;
    names(2) := 'Priya';

    names.EXTEND;
    names(3) := 'Amit';

    FOR i IN 1 .. names.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(names(i));
    END LOOP;
END;
/

Output

Rahul
Priya
Amit

Example 3: Deleting an Element

DECLARE
    TYPE num_tab IS TABLE OF NUMBER;

    nums num_tab := num_tab(10,20,30,40);

BEGIN
    nums.DELETE(2);

    FOR i IN nums.FIRST .. nums.LAST LOOP
        IF nums.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE(nums(i));
        END IF;
    END LOOP;
END;
/

Output

10
30
40

Example 4: Nested Table as a Column

Step 1: Create Nested Table Type

CREATE TYPE phone_list AS TABLE OF VARCHAR2(15);
/

Step 2: Create Table

CREATE TABLE CUSTOMER
(
    CUSTOMER_ID NUMBER,
    CUSTOMER_NAME VARCHAR2(50),
    PHONE_NUMBERS phone_list
)
NESTED TABLE PHONE_NUMBERS STORE AS PHONE_STORE;

Step 3: Insert Data

INSERT INTO CUSTOMER
VALUES
(
    101,
    'Rahul',
    phone_list('9876543210','9123456789')
);

Collection Methods

Nested Table vs VARRAY