Returning a Table from an Oracle Function (Concept & Mechanism)

In Oracle, a function cannot directly return a database table.
However, it can return a collection type (a table in memory), which SQL can then treat as a virtual table.
To support this, Oracle requires a structured set of components defined at the SQL level.

1. Row Type (Single Result Row Definition)

A row type describes the structure of one record in the returned result set.
It defines the columns and their data types, similar to a table row.

Purpose:

• Gives SQL a known structure for each returned record.
• Must be a SQL object type (not PL/SQL-only), because the result must be usable in SQL queries.

2. Table Type (Collection of Rows)

A table type defines a list of row-type elements.
This acts as an in-memory table that can hold multiple result rows.

Purpose:

• Represents the entire result set.
• Must also be a SQL-level nested table type so it can be used in the SQL engine.

3. Function Returning the Table Type

The function itself returns a value whose type is the table-collection type.

There are two ways the function may return results:

A) Normal (Bulk Return)

• The function collects all result rows first.
• Returns the entire collection at once.
• Works best when the result set is small or moderate in size.

Characteristics:

B) Pipelined Return (Streaming Return)

The function streams rows as they are produced, without constructing the whole result in memory.

Characteristics:

Concept:
Each row is emitted using PIPE ROW, enabling caller to start receiving data instantly.

4. Querying the Function Output

SQL uses the TABLE(...) operator to treat the returned collection as if it were a regular relational table.

This allows:

• SELECT from the result
• JOIN with other tables
• WHERE, ORDER BY, GROUP BY
• Storing in views or materialized views

The database effectively converts the in-memory list into a row source for the SQL optimizer.

Summary: What Each Component Does

 When to Choose Which Return Style