What Are Pipelined Table Functions?
A pipelined table function is a PL/SQL function that returns a collection of rows, but instead of building the entire result set in memory before returning it, the function pipes rows back to the caller one at a time (or in small batches) as they are produced. The calling SQL statement can consume these rows through the TABLE() operator just as if it were querying a regular database table or view.
This approach offers two major advantages. First, it dramatically reduces memory consumption because you never need to hold the entire result set in PGA memory. Second, the calling query can begin processing rows immediately rather than waiting for the function to complete, which improves perceived response times.
Building Your First Pipelined Function
Every pipelined table function requires a SQL object type (or a PL/SQL record type wrapped in a collection type) to define the shape of each row it returns. Here is a complete working example:
-- Step 1: Create the row type and collection type
CREATE OR REPLACE TYPE t_employee_row AS OBJECT (
employee_id NUMBER,
full_name VARCHAR2(200),
annual_salary NUMBER
);
/
CREATE OR REPLACE TYPE t_employee_tab IS TABLE OF t_employee_row;
/
-- Step 2: Create the pipelined function
CREATE OR REPLACE FUNCTION get_enriched_employees(
p_department_id IN NUMBER
)
RETURN t_employee_tab PIPELINED
AS
BEGIN
FOR rec IN (
SELECT employee_id,
first_name || ' ' || last_name AS full_name,
salary * 12 AS annual_salary
FROM employees
WHERE department_id = p_department_id
)
LOOP
PIPE ROW(t_employee_row(
rec.employee_id,
rec.full_name,
rec.annual_salary
));
END LOOP;
RETURN;
END;
/
-- Step 3: Query it like a table
SELECT *
FROM TABLE(get_enriched_employees(30))
WHERE annual_salary > 50000;Real World Use Cases
Pipelined table functions shine in several common scenarios. Data transformation pipelines where you need to read from one source, apply complex business logic, and present the results as a queryable row set are a natural fit. They are also excellent for wrapping external data sources (flat files, web service responses, or data from other systems via database links) and presenting them as if they were local tables.
Another powerful use case is creating parameterized views. Regular database views cannot accept parameters, but a pipelined function called through the TABLE() operator can. This gives you the filtering flexibility of a procedure with the composability of a view.
Performance Considerations
Because the function streams rows rather than materializing them all at once, the optimizer treats pipelined functions differently from regular table functions. In Oracle 12c and later, the optimizer can push predicates down into pipelined functions in some cases, further improving performance. However, you should be aware that statistics on pipelined function results are not available to the optimizer in the same way they are for regular tables, so you may need to use the CARDINALITY or OPT_ESTIMATE hints to help the optimizer make good join decisions.
Oracle 19c Through 23ai Improvements
In Oracle 19c, the optimizer became better at estimating row counts from pipelined functions when the function is marked as DETERMINISTIC. Oracle 21c introduced polymorphic table functions which offer an alternative approach for some use cases that previously required pipelined functions. In 23ai, further improvements to the SQL engine’s ability to inline simple pipelined functions have been made, reducing call overhead.
That said, pipelined table functions remain the go to choice when you need procedural logic (loops, conditionals, exception handling) to generate your result set, because polymorphic table functions are limited to transforming existing row sources rather than generating new ones from scratch.
Gotchas to Watch For
If the caller does not consume all rows (for example, because of a ROWNUM filter or because the application only fetches the first page of results), Oracle will raise a NO_DATA_NEEDED exception inside your function. You do not need to handle this exception explicitly because the PL/SQL runtime handles it for you, but you should be aware that your function may not always run to completion. If you have cleanup logic, place it in an exception handler that catches NO_DATA_NEEDED or use it to clean up resources gracefully.
Also remember that each call to PIPE ROW returns control briefly to the consumer, so avoid placing expensive setup operations inside the loop. Do your initialization before the loop begins.