Query Oracle 26ai Nested Tables with TABLE and Collection SQL

Define an element type, collection type and parent table

The three diagrams retain the PETSTORE sales model. They distinguish a typed reference from a scalar identifier and include the storage clause needed by the parent table. PRODUCT_TYPE and CUSTOMER_TYPE must exist before the displayed DDL. REF values designate stored row objects; these declarations neither create those targets nor enforce their continued existence.

A self-contained practice example

CREATE TYPE lesson6_line_t AS OBJECT (
  product_id     NUMBER,
  item_qty       NUMBER,
  detail_amount  NUMBER(10,2)
);
/
CREATE TYPE lesson6_line_list_t AS TABLE OF lesson6_line_t;
/
CREATE TABLE lesson6_sales (
  sale_id  NUMBER PRIMARY KEY,
  details  lesson6_line_list_t
) NESTED TABLE details STORE AS lesson6_sales_details_nt;

INSERT INTO lesson6_sales VALUES (
  1001, lesson6_line_list_t(
    lesson6_line_t(501, 2, 40.00),
    lesson6_line_t(502, 1, 15.00)
  )
);
INSERT INTO lesson6_sales VALUES (1002, lesson6_line_list_t());
INSERT INTO lesson6_sales VALUES (1003, NULL);
COMMIT;

Return a collection or unnest its elements

Select the collection column when an application needs the entire typed value:

SELECT s.sale_id, s.details
FROM lesson6_sales s
ORDER BY s.sale_id;

This produces two result columns. The details column is a collection value, whose display depends on the client. It is not automatically a flat list of product columns, and it should not universally be described as a locator. If a report needs one row per detail, use a TABLE expression in the FROM clause:

SELECT s.sale_id, d.product_id, d.item_qty, d.detail_amount
FROM lesson6_sales s, TABLE(s.details) d
WHERE d.detail_amount >= 20
ORDER BY s.sale_id, d.product_id;

The parent alias s supplies the collection, and d names the unnested elements. Because each element is an object, its attributes are exposed as d.product_id and d.item_qty. The illustrative matching row is sale 1001, product 501, quantity 2, amount 40.00. This form returns matching elements, rather than a sale row with an embedded list.

TABLE distributes the collection elements into relational rows. It does not introduce a permanent ordinal or sort the results. ORDER BY makes the reporting order explicit. If two details have the same product identifier, add a suitable line identifier to the model when a unique ordering or individual line identity is required.

With a nested table of scalar values, the unnested value is named COLUMN_VALUE instead of exposing object attributes. You will see that form in the tag example below. Choose projections to match the declared element datatype, rather than copying attribute names from an unrelated example.

Find qualifying parents without multiplying them

Suppose the question is which sales contain product 501, rather than which individual details match. A correlated EXISTS returns each qualifying parent once:

SELECT s.sale_id
FROM lesson6_sales s
WHERE EXISTS (
  SELECT 1
  FROM TABLE(s.details) d
  WHERE d.product_id = 501
)
ORDER BY s.sale_id;

The illustrative result is sale 1001. Even if several details in that sale meet the predicate, EXISTS asks only whether a match exists. A direct unnesting query can repeat the sale_id for multiple matching elements. Select the query shape deliberately instead of applying DISTINCT automatically to hide duplicates.

You can also join unnested product_id values to an ordinary product table when one exists. That join would require an actual product table definition and a chosen key relationship. A scalar attribute named product_id does not create a foreign key automatically. Similarly, the PETSTORE diagrams' REF attributes need explicit integrity design if target existence matters.

Aggregate details and preserve empty parents

An ordinary unnesting query produces no element rows for an empty collection. To preserve the sale and report a zero total, use the collection outer-join form documented by Oracle:

SELECT s.sale_id,
       COUNT(d.product_id) AS product_rows,
       NVL(SUM(d.detail_amount), 0) AS detail_total
FROM lesson6_sales s, TABLE(s.details) (+) d
GROUP BY s.sale_id
ORDER BY s.sale_id;

This illustrates totals of 55.00, 0 and 0 for sales 1001, 1002 and 1003. COUNT(d.product_id) illustrates two populated product identifiers for the first sale and zero for the others. It is not a universal count of all elements if real details can have null product identifiers. COUNT(*) would count the null-extended parent row, so it would be misleading for this particular report.

The (+) belongs to the collection expression here. It tells Oracle to return a parent row with null element columns when the collection contributes no rows, including a null collection. This is a documented collection-query pattern, not a claim that every historical outer-join form should be preferred for ordinary joins.

Be careful with a later WHERE predicate on an element attribute: it can remove the null-extended rows and undo the intended parent preservation. Decide whether a predicate qualifies sales or limits the elements included in an aggregate. Those are different reporting requirements. Test populated, empty and null collections when revising the query.

Distinguish empty collections from null collections

SELECT s.sale_id
FROM lesson6_sales s
WHERE s.details IS EMPTY
ORDER BY s.sale_id;

SELECT s.sale_id
FROM lesson6_sales s
WHERE s.details IS NULL
ORDER BY s.sale_id;

The first query illustrates sale 1002, and the second sale 1003. An initialized empty collection exists and contains no elements; a null collection has no supplied collection value. IS EMPTY applied to a null collection produces an unknown result, which does not pass a WHERE filter. Do not use IS NOT EMPTY as a substitute for every null check.

A collection containing an element with null attributes is another case: it is not empty merely because those attributes contain no values. Likewise, SQL filters normally retain only true conditions, not unknown conditions. Make absent data semantics part of the application contract rather than interpreting every missing value as an empty list.

Use membership and multiset tests on comparable elements

Scalar tags make these operators easy to demonstrate without requiring object comparison methods. Create a separate schema-level collection type:

CREATE TYPE lesson6_tag_list_t AS TABLE OF VARCHAR2(30 CHAR);
/
SELECT 'matching' AS result
FROM dual
WHERE 'oracle' MEMBER OF lesson6_tag_list_t('oracle', 'sql');

SELECT 'covered' AS result
FROM dual
WHERE lesson6_tag_list_t('oracle', 'sql')
      SUBMULTISET OF lesson6_tag_list_t('sql', 'oracle', 'plsql');

The first query illustrates matching and the second covered. MEMBER OF tests element membership. SUBMULTISET OF tests whether the left collection is contained in the right with the necessary multiplicities. Two copies of a tag on the left require enough matching occurrences on the right. Neither expression means that collections have been deduplicated.

Element datatypes must support the required comparison. For object elements, inspect comparison rules and methods before assuming whole-object membership behaves like a simple identifier check. The earlier EXISTS query compares a scalar product attribute and avoids that ambiguity. Null values can also produce unknown comparison results, so include them in meaningful application tests.

Query a collection variable using a SQL type

The following PL/SQL block uses the schema-level lesson6_line_list_t already defined above. SQL can therefore recognize the element structure in TABLE(l_lines):

SET SERVEROUTPUT ON
DECLARE
  l_lines lesson6_line_list_t := lesson6_line_list_t(
    lesson6_line_t(502, 1, 15.00),
    lesson6_line_t(501, 2, 40.00)
  );
BEGIN
  FOR r IN (
    SELECT d.product_id, d.detail_amount
    FROM TABLE(l_lines) d
    ORDER BY d.product_id
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(r.product_id || ': ' || r.detail_amount);
  END LOOP;
END;
/

The illustrative traversal visits product 501 before 502 because of ORDER BY, although the constructor listed 502 first. Client settings control DBMS_OUTPUT visibility and numeric formatting. This block neither inserts collection elements nor allocates a persisted storage table.

Do not generalize this example to every locally declared PL/SQL collection of records. SQL interoperability depends on where the type is declared, its element datatype and the operation. A schema-level SQL collection is the straightforward choice here. BULK COLLECT can populate collections from queries, but it does not remove those type-compatibility rules or justify fetching an arbitrarily large dataset into memory.

Build a collection result with COLLECT

SELECT s.sale_id,
       CAST(COLLECT(TO_CHAR(d.product_id))
            AS lesson6_tag_list_t) AS product_labels
FROM lesson6_sales s, TABLE(s.details) d
GROUP BY s.sale_id;

COLLECT aggregates the selected scalar expressions, and CAST supplies the named result collection type. Here the tags type is reused to hold product identifiers converted to text. Only the populated sale contributes rows to this inner unnesting example. The collection value contains labels for products 501 and 502, but the example does not promise an element order.

This is a query result, not CREATE TABLE and not a new persisted nested-table column. Aggregating existing relational child rows into a typed collection uses the same idea. Consider duplicate handling and null expressions explicitly when designing that interface. Avoid assuming that casting a value enforces the business meaning implied by a type's name.

Modify selected nested-table elements

Nested tables support SQL element operations. This optional continuation updates one product's line amount within sale 1001:

UPDATE TABLE (
  SELECT s.details FROM lesson6_sales s WHERE s.sale_id = 1001
) d
SET d.detail_amount = 45.00
WHERE d.product_id = 501;

The parent selector uses a primary key to identify one collection. The element predicate may still update multiple details if product_id repeats inside that collection. Use a line key when exactly one element must be targeted. After this illustrative change, rerunning the total query would show 60.00 for sale 1001. Choose COMMIT or ROLLBACK according to the owning transaction.

SQL changes to the nested collection do not automatically maintain separate stored totals in a sales header. Applications must specify the consistency rule and implement it appropriately. VARRAYs differ here: SQL does not support their piecewise element DML, even though TABLE can query them. Choose based on actual update requirements, not an unsupported blanket claim that one collection is always faster.

Advanced context: Oracle Any types

Nested Table - Quiz

Oracle 26ai references: collection querying and DML, multiset conditions, COLLECT and Any types.