Oracle Nested Tables

A nested table is an Oracle collection type that lets a single column hold a set of values (or a set of objects). Conceptually, it looks like “a table inside a row.” Physically, Oracle stores the nested rows in a separate store table and links them back to the owning row using internal identifiers. You query the nested rows with the TABLE() operator.

In Oracle 23ai, nested tables remain fully supported—especially for PL/SQL collection processing and for legacy object-relational designs. However, for modern document-style data modeling, teams often prefer JSON and (when appropriate) JSON Relational Duality Views. This lesson focuses on the core skill: creating a nested table structure and querying it correctly.

Nested tables are defined as SQL types. A common pattern is: (1) define an object type for one “nested row,” then (2) define a nested table type as TABLE OF that object.

CREATE TYPE empobj AS OBJECT (
  emp_name  VARCHAR2(30),
  emp_phone VARCHAR2(20),
  sal       NUMBER(9,2)
);
/

CREATE TYPE empobj_tab AS TABLE OF empobj;
/

The owner table contains the nested-table column. The NESTED TABLE ... STORE AS ... clause tells Oracle the name of the store table that will physically hold the nested rows.

CREATE TABLE deptnest (
  deptno    NUMBER       CONSTRAINT deptnest_pk PRIMARY KEY,
  dname     VARCHAR2(20) NOT NULL,
  location  VARCHAR2(20),
  emp       empobj_tab
)
NESTED TABLE emp STORE AS emp_tab;

Practical note: Oracle adds hidden columns in the store table to maintain the link to the owning row. You normally do not query those hidden columns directly; you use TABLE(deptnest.emp) instead.

You can insert nested rows using collection constructors. For a nested table of objects, construct the nested table with empobj_tab(...) and each nested row with empobj(...).

INSERT INTO deptnest (deptno, dname, location, emp)
VALUES (
  10,
  'Finance',
  'Detroit',
  empobj_tab(
    empobj('Smith',  '555-0100', 90000),
    empobj('Jones',  '555-0110', 85000),
    empobj('Baker',  '555-0120', 82000)
  )
);

INSERT INTO deptnest (deptno, dname, location, emp)
VALUES (
  20,
  'Accounting',
  'Ann Arbor',
  empobj_tab(
    empobj('Able',    '555-0200', 78000),
    empobj('Barrett', '555-0210', 80000)
  )
);

Use TABLE() to unnest the collection into a row source. This is the canonical pattern for reporting and joins.

SELECT d.deptno,
       d.dname,
       e.emp_name,
       e.emp_phone,
       e.sal
FROM deptnest d,
     TABLE(d.emp) e
ORDER BY d.deptno, e.emp_name;

You can update elements via a correlated reference to TABLE(). You can also delete nested rows using the same approach.

-- Give Finance employees a 5% raise
UPDATE TABLE(
  SELECT d.emp
  FROM deptnest d
  WHERE d.deptno = 10
) e
SET e.sal = e.sal * 1.05;

-- Remove a single nested row (example: delete Baker from Finance)
DELETE FROM TABLE(
  SELECT d.emp
  FROM deptnest d
  WHERE d.deptno = 10
) e
WHERE e.emp_name = 'Baker';

• PL/SQL collection workflows: Passing sets into procedures, transforming sets in memory, or unnesting with TABLE() for SQL operations.
• Bulk processing patterns: Using collections alongside set-based SQL and minimizing procedural overhead when assembling rowsets.
• Legacy object-relational schemas: Maintaining existing designs where nested tables are already part of the contract.

If your primary goal is modern application-facing document structures, favor JSON (and Duality Views when you want a relational base table with a JSON-facing interface). Use nested tables when the schema and workload benefit from an Oracle-native collection that is directly queryable with SQL.

Oracle also supports VARRAY types. The major distinction is that a VARRAY has a declared maximum size and is often treated as a single unit, whereas a nested table is unbounded and is typically unnested into rows with TABLE().