PL/SQL & T-SQL (Oracle and SQL Server) Module 14 – Performance: plans, PL/SQL called from SQL and table functions
Pipelined table functions and SQL macros
Stream rows from PL/SQL to a query with pipelined table functions, clean up with NO_DATA_NEEDED when it stops early, and build queries with table SQL macros.
What you will learn
- Write a pipelined table function that hands rows to a query with PIPE ROW
- Handle NO_DATA_NEEDED so a function cleans up when its query stops reading early
- Choose between a pipelined function, a table SQL macro and a polymorphic table function
- Predict the query a table SQL macro puts in place of its call
- Explain what a parallel-enabled pipelined function needs from its input
Before you start
What you’ll learn · Before you start
What you will learn
- Write a pipelined table function that hands rows to a query with PIPE ROW
- Handle NO_DATA_NEEDED so a function cleans up when its query stops reading early
- Choose between a pipelined function, a table SQL macro and a polymorphic table function
- Predict the query a table SQL macro puts in place of its call
- Explain what a parallel-enabled pipelined function needs from its input
Before you start
On this page
A report that shows the first 6 months of a 240-month loan schedule needs 6 rows, yet a table function that returns a collection builds all 240 and holds them in memory before the query sees month 1. pipelined table function: A PL/SQL table function declared PIPELINED that hands each row to the query with PIPE ROW as soon as it is built, instead of building a whole collection and returning it at the end. hand each row to the query as soon as it is built, so early rows arrive early and the work stops once the query has enough.
You will write a pipelined schedule, let it clean up when a query stops early, and choose between a pipelined function, a table SQL macro and a polymorphic table function.
A pipelined function hands each row over with PIPE ROW
The practice function emi_schedule builds a loan’s repayment plan: per month, the part that repays the loan, the
interest and the balance left. Each payment is the same
Equated monthly instalment: the same payment every month, worked out so that it repays a loan and its interest over a fixed number of months.:
17,254.84 a month for 100,000 at 12 % a year over 6 months. Step through the rows as they leave:
The drawing shows the whole scene; its 5 steps are listed below.
Every step as text
- The query opens the function. The query asks emi_schedule for the rows of a loan of 100,000 at 12 % a year over 6 months. The function works out the monthly payment once: 17,254.84.
- Month 1 is built and piped. Interest for month 1 is 1 % of 100,000, so 1,000.00; the rest of the payment, 16,254.84, repays the loan and leaves 83,745.16. PIPE ROW hands this row to the query and the loop goes on.
- Month 2 follows the same way. Interest is now 1 % of the smaller balance, 837.45, so more of the payment repays the loan: 16,417.39. The function never holds more than the row it is building.
- The last month clears the loan. Month 6 repays whatever is left, 17,083.98, with 170.84 of interest, so the balance ends at 0.00 instead of a rounding remainder. Its payment is 17,254.82, 0.02 less than the others.
- RETURN ends the function. After the last PIPE ROW the function runs RETURN with no value: every row has already gone to the query. The query has six rows and a total interest of 3,529.02.
Pipelined table functions say so in the header, RETURN emi_rows_t PIPELINED, with a collection type declared at
schema level or in a package, SQL element types, never an associative array. PIPE ROW sends one element to the query without giving up control,
and the final RETURN carries no value. A query reads the function like a table:
-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- A pipelined table function that builds a loan's monthly schedule one row at a time and hands each row to the
-- query with PIPE ROW. Amounts are rounded to 2 places; the last month repays whatever balance is left.
SET FEEDBACK OFF
-- Drop what an earlier run left, the function first because it depends on the types.
DROP FUNCTION IF EXISTS emi_schedule;
DROP TYPE IF EXISTS emi_rows_t;
DROP TYPE IF EXISTS emi_row_t;
-- The row and the collection the function returns: SQL types, so a query can read them.
CREATE TYPE emi_row_t AS OBJECT (
month_num NUMBER(4),
principal_part NUMBER(14, 2),
interest NUMBER(14, 2),
balance NUMBER(14, 2)
);
/
CREATE TYPE emi_rows_t AS TABLE OF emi_row_t;
/
CREATE FUNCTION emi_schedule (
p_principal IN NUMBER, -- the amount borrowed
p_annual_rate IN NUMBER, -- yearly interest rate, in per cent
p_months IN NUMBER -- number of monthly payments
) RETURN emi_rows_t PIPELINED
IS
v_rate NUMBER := p_annual_rate / 100 / 12;
v_payment NUMBER;
v_balance NUMBER := p_principal;
v_int NUMBER;
v_part NUMBER;
BEGIN
-- Equal monthly payment: P * r * (1 + r)^n / ((1 + r)^n - 1), rounded to 2 places.
v_payment := ROUND(p_principal * v_rate * POWER(1 + v_rate, p_months)
/ (POWER(1 + v_rate, p_months) - 1), 2);
FOR m IN 1 .. p_months LOOP
v_int := ROUND(v_balance * v_rate, 2);
IF m = p_months THEN
v_part := v_balance; -- the last payment absorbs the rounding
ELSE
v_part := v_payment - v_int;
END IF;
v_balance := v_balance - v_part;
PIPE ROW (emi_row_t(m, v_part, v_int, v_balance));
END LOOP;
RETURN; -- nothing to return: every row was piped
END emi_schedule;
/
SET FEEDBACK ON
-- 1. Six payments on 100,000 at 12 % a year. Expected: six rows, month 1 = 16254.84, 1000.00, 83745.16;
-- month 6 = 17083.98, 170.84, 0.00 (examples/model/stream_model.py computes the same rows).
SELECT month_num, principal_part, interest, balance
FROM emi_schedule(100000, 12, 6);
-- 2. Totals over the same rows. Expected: total_interest 3529.02, total_paid 103529.02.
SELECT SUM(interest) AS total_interest, SUM(principal_part + interest) AS total_paid
FROM emi_schedule(100000, 12, 6);
-- 3. A 240-month loan, read only as far as the report needs. Expected: six rows, months 1 to 6.
SELECT month_num, principal_part, interest, balance
FROM emi_schedule(100000, 12, 240)
FETCH FIRST 6 ROWS ONLY;
-- Leave the schema as it was.
DROP FUNCTION emi_schedule;
DROP TYPE emi_rows_t;
DROP TYPE emi_row_t;"""emi_schedule.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""
from oracle_session import Lab
with Lab() as lab:
lab.sqlplus("pipeline/emi_schedule.sql")Output
$ sqlplus -s lab@//localhost/FREEPDB1 @pipeline/emi_schedule.sql
MONTH_NUM PRINCIPAL_PART INTEREST BALANCE
---------- -------------- ---------- ----------
1 16254.84 1000 83745.16
2 16417.39 837.45 67327.77
3 16581.56 673.28 50746.21
4 16747.38 507.46 33998.83
5 16914.85 339.99 17083.98
6 17083.98 170.84 0
6 rows selected.
TOTAL_INTEREST TOTAL_PAID
-------------- ----------
3529.02 103529.02
1 row selected.
MONTH_NUM PRINCIPAL_PART INTEREST BALANCE
---------- -------------- ---------- ----------
1 101.09 1000 99898.91
2 102.1 998.99 99796.81
3 103.12 997.97 99693.69
4 104.15 996.94 99589.54
5 105.19 995.9 99484.35
6 106.25 994.84 99378.1
6 rows selected.
Function dropped.
Type dropped.
Type dropped.
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 emi_schedule_session.py
Its expected rows come from this model, with the same rounding: a generator’s yield plays PIPE ROW, and a
list plays a function that returns the whole collection. Change the loan and run it:
"""A model of the lesson's pipelined EMI schedule, in Python's own terms.
A Python generator hands back one value per yield, the way a pipelined table
function hands back one row per PIPE ROW. Closing a generator early raises
GeneratorExit at the paused yield, the way PIPE ROW raises NO_DATA_NEEDED when
the query needs no more rows. Amounts are rounded half up to 2 places, as
Oracle's ROUND rounds these positive amounts. The model has no batches: the
PL/SQL runtime hands piped rows over in batches, so a real function can build
a few rows more than the query reads before it is stopped.
"""
from decimal import ROUND_HALF_UP, Decimal
from itertools import islice
CENT = Decimal("0.01")
def monthly_payment(principal, annual_rate, months):
"""The equated monthly instalment, as emi_schedule.sql computes it."""
r = Decimal(annual_rate) / 100 / 12
f = (1 + r) ** months
return (Decimal(principal) * r * f / (f - 1)).quantize(CENT, ROUND_HALF_UP)
def schedule(principal, annual_rate, months, built):
"""Yield (month, principal part, interest, balance) one row at a time."""
r = Decimal(annual_rate) / 100 / 12
pay = monthly_payment(principal, annual_rate, months)
balance = Decimal(principal)
try:
for month in range(1, months + 1):
interest = (balance * r).quantize(CENT, ROUND_HALF_UP)
# The last month repays what is left, so the balance ends at 0.00.
part = balance if month == months else pay - interest
balance -= part
built.append(month)
yield month, part, interest, balance
except GeneratorExit:
built.append("stopped") # the NO_DATA_NEEDED moment: clean up here
raise
built = []
rows = list(schedule(100000, 12, 6, built))
print("Loan 100000 at 12% a year over 6 months, payment",
monthly_payment(100000, 12, 6))
print("month principal interest balance")
for month, part, interest, balance in rows:
print(f"{month:>5} {part:>9} {interest:>8} {balance:>9}")
total_interest = sum(row[2] for row in rows)
print("total interest", total_interest, "| last payment", rows[-1][1] + rows[-1][2])
# A collection-returning function builds every row before the caller sees one.
built_list = []
whole = list(schedule(100000, 12, 240, built_list))
first_six = whole[:6]
print("collection: rows built before month 1 reached the caller:", len(built_list))
# A pipelined function hands rows over as it builds them; stop after 6.
built_pipe = []
rows_pipe = schedule(100000, 12, 240, built_pipe)
first_six_pipe = list(islice(rows_pipe, 6))
rows_pipe.close()
print("pipelined: rows built for a query that reads 6:",
sum(1 for b in built_pipe if b != "stopped"), "| then", built_pipe[-1])
same = first_six == first_six_pipe
print("same first six rows both ways:", same)Output
Loan 100000 at 12% a year over 6 months, payment 17254.84
month principal interest balance
1 16254.84 1000.00 83745.16
2 16417.39 837.45 67327.77
3 16581.56 673.28 50746.21
4 16747.38 507.46 33998.83
5 16914.85 339.99 17083.98
6 17083.98 170.84 0.00
total interest 3529.02 | last payment 17254.82
collection: rows built before month 1 reached the caller: 240
pipelined: rows built for a query that reads 6: 6 | then stopped
same first six rows both ways: True
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 stream_model.py
Runs on this device, in your browser. The first run downloads Python (about 13.5 MB), which is kept for the next runs.
Your run, in this browser
Month 1 pays 1,000.00 of interest; month 6 pays 170.84 and clears the balance to 0.00, for 3,529.02 in all. For the 240-month loan the collection style built 240 rows before month 1 reached the caller; the streaming style built 6.
A rounding remainder in the last row
The same rounded payment every month leaves -0.02 after month 6 here. Clamping it to 0 makes the rows disagree with the money paid: let the last month repay exactly the balance left.
Checkpoint
Check: Rows handed over with PIPE ROW
What the function sees when its query stops early
A query that reads numbered_rows(1000) with WHERE ROWNUM <= 3 needs 3 of the 1,000 rows. Once it has them, the
PIPE ROW at which the function is paused raises the predefined exception
NO_DATA_NEEDED: The predefined exception ORA-06548 that PIPE ROW raises in a pipelined table function when the query reading it needs no more rows. The query keeps its rows; a handler is needed only to release resources., ORA-06548, so the loop goes no further, and the query finishes
normally with its three:
The drawing shows the whole scene; its 5 steps are listed below.
Every step as text
- Rows go to the query. numbered_rows pipes 1, 2 and 3, pausing at each PIPE ROW while the query takes the row. The runtime delivers piped rows in batches, so a function can also run a little ahead of what the query has read.
- The query needs no more. The query asked for ROWNUM <= 3. Once it holds three rows it needs nothing else from the function, which is still inside its loop with up to 997 rows to go.
- PIPE ROW raises the exception. The PIPE ROW at which the function is paused raises the predefined exception NO_DATA_NEEDED, ORA-06548: in the lab run, the one that handed over row 3. No more rows are built.
- The handler releases resources. The WHEN NO_DATA_NEEDED handler runs: this is where a function closes a cursor or a file it opened, then ends with RETURN. Without a handler the function simply ends here.
- The query is unaffected. Either way the query finishes with its three rows and no error. Only an OTHERS handler that catches the exception and raises another error turns this normal stop into a failed query.
Unhandled, the exception simply ends the function’s run; it never reaches the query. A handler matters when the
function holds something to release, such as a cursor or a file it opened: it closes it and ends with RETURN
(re-raising NO_DATA_NEEDED does the same). Oracle’s PIPE ROW page adds that piped rows can travel in batches,
so a function may stop a few rows past the last one read. In the lab run below it stops at row 3, the last row the
query read:
-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- What a pipelined function sees when the query that reads it stops early. PIPE ROW raises the predefined
-- exception NO_DATA_NEEDED (ORA-06548) once the query needs no more rows; the function cleans up in its handler.
-- The runtime hands piped rows to the query in batches, so the row where the function stops can be later than the
-- last row the query read: the DBMS_OUTPUT lines show where it stopped on your run.
SET FEEDBACK OFF
SET SERVEROUTPUT ON
DROP FUNCTION IF EXISTS numbered_rows;
DROP FUNCTION IF EXISTS numbered_rows_others;
DROP TYPE IF EXISTS row_numbers_t;
CREATE TYPE row_numbers_t AS TABLE OF NUMBER;
/
-- Handles NO_DATA_NEEDED: the place to close what the function opened (a cursor, a file, a session on a service).
CREATE FUNCTION numbered_rows (p_last IN NUMBER)
RETURN row_numbers_t PIPELINED
IS
v_built NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('numbered_rows: started');
FOR i IN 1 .. p_last LOOP
v_built := i;
PIPE ROW (i);
END LOOP;
DBMS_OUTPUT.PUT_LINE('numbered_rows: all ' || v_built || ' rows piped, clean-up done');
RETURN;
EXCEPTION
WHEN NO_DATA_NEEDED THEN
DBMS_OUTPUT.PUT_LINE('numbered_rows: stopped while piping row ' || v_built || ', clean-up done');
RETURN;
END numbered_rows;
/
-- The trap: an OTHERS handler with no NO_DATA_NEEDED handler before it turns the early stop into an error.
CREATE FUNCTION numbered_rows_others (p_last IN NUMBER)
RETURN row_numbers_t PIPELINED
IS
BEGIN
FOR i IN 1 .. p_last LOOP
PIPE ROW (i);
END LOOP;
RETURN;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'numbered_rows_others failed: ' || SQLERRM);
END numbered_rows_others;
/
SET FEEDBACK ON
-- 1. The query reads every row. Expected: 1 to 5, then "all 5 rows piped, clean-up done".
SELECT COLUMN_VALUE AS n FROM numbered_rows(5);
-- 2. The query needs three of 1,000 rows. Expected: 1, 2, 3, then, in the lab, "stopped while piping row 3": the
-- PIPE ROW that handed over row 3 raised NO_DATA_NEEDED once the query had its three rows.
SELECT COLUMN_VALUE AS n FROM numbered_rows(1000) WHERE ROWNUM <= 3;
-- 3. The same early stop through the OTHERS handler. Expected: no rows and ORA-20001 that names ORA-06548.
SELECT COLUMN_VALUE AS n FROM numbered_rows_others(1000) WHERE ROWNUM <= 3;
-- The fix for 3: a NO_DATA_NEEDED handler BEFORE the OTHERS handler.
CREATE OR REPLACE FUNCTION numbered_rows_others (p_last IN NUMBER)
RETURN row_numbers_t PIPELINED
IS
BEGIN
FOR i IN 1 .. p_last LOOP
PIPE ROW (i);
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_NEEDED THEN
RETURN; -- expected: the query has what it needs
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'numbered_rows_others failed: ' || SQLERRM);
END numbered_rows_others;
/
-- 4. Expected now: 1, 2, 3 and no error.
SELECT COLUMN_VALUE AS n FROM numbered_rows_others(1000) WHERE ROWNUM <= 3;
DROP FUNCTION numbered_rows;
DROP FUNCTION numbered_rows_others;
DROP TYPE row_numbers_t;
SET SERVEROUTPUT OFF"""no_data_needed.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""
from oracle_session import Lab
with Lab() as lab:
lab.sqlplus("pipeline/no_data_needed.sql", errors=1)Output
$ sqlplus -s lab@//localhost/FREEPDB1 @pipeline/no_data_needed.sql
N
----------
1
2
3
4
5
5 rows selected.
numbered_rows: started
numbered_rows: all 5 rows piped, clean-up done
N
----------
1
2
3
3 rows selected.
numbered_rows: started
numbered_rows: stopped while piping row 3, clean-up done
ERROR:
ORA-20001: numbered_rows_others failed: ORA-06548: no more rows needed
ORA-06512: at "LAB.NUMBERED_ROWS_OTHERS", line 11
no rows selected
Function created.
N
----------
1
2
3
3 rows selected.
Function dropped.
Function dropped.
Type dropped.
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 no_data_needed_session.py
Query 3 is the failure to remember: it looks like a bug in the query, not in the handler.
An OTHERS handler that sees the stop as a failure
WHEN OTHERS THEN RAISE_APPLICATION_ERROR(...) catches NO_DATA_NEEDED too, so the three-row query fails with
ORA-20001. Put WHEN NO_DATA_NEEDED THEN RETURN; before WHEN OTHERS, as query 4 does.
Checkpoint
Check: NO_DATA_NEEDED when a query stops early
Pipelined function, table SQL macro or polymorphic table function
SQL macros are another way to put PL/SQL behind a FROM clause. A
table SQL macro: A PL/SQL function marked SQL_MACRO(TABLE) that returns the text of a query; Oracle uses that query in place of the call in the FROM clause, so no PL/SQL runs for each row. returns the text of a query, and Oracle uses that query in place
of the call. With SQL macros no PL/SQL runs per row: the optimizer reads the returned query as if it
were written into the statement; the scalar kind, for expressions, belongs to
the lesson on PL/SQL functions called from SQL. A
polymorphic table function: A table function that takes a whole table as an argument and decides its result columns from that table, so one function can serve many different tables. takes a whole table as an argument and
decides its result columns from it. A pipelined function marked PARALLEL_ENABLE lets a parallel query split its
input rows across server processes.
| Compared | Pipelined table function | Table SQL macro | Polymorphic table function |
|---|---|---|---|
| Rows come from | PL/SQL builds them | Tables the text names | The table passed in |
| PL/SQL per row | Yes | None | Only to add values |
| Fits | A schedule, a series | A query with parameters | Columns decided per table |
The schedule is the first column’s case: its rows exist nowhere until the function computes them. A report of the orders in one status is the second column’s: every value it shows already sits in a table.
Checkpoint
Check: Choosing the kind of table function
Table SQL macros: the query the optimizer reads instead of the call Pro: opens with a Pro, Premium or Ultimate pass
A table SQL macro turns a reusable query with parameters into something a FROM clause can call, while the
optimizer still sees every table and join. This part builds one on the practice orders, reads its plan, and sets
out the rules a macro must keep.
In this part:
- What Oracle puts in place of the macro call
- The rules a table macro has to keep
- One orders report written three ways, and what each costs
2 runnable examples (Python, SQL) · about 5 minutes
Opening this part with your pass…
This part opens with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so this part can’t be opened at the moment. The free part above stands on its own. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
Polymorphic table functions and pipelined functions in parallel Pro: opens with a Pro, Premium or Ultimate pass
Two tools handle the cases the first part leaves open: a table whose columns are not known when the function is written, and a pipelined function fed so many rows that one process is too slow. This part builds a polymorphic table function on the practice tables and sets out what a parallel pipelined function needs.
In this part:
- A polymorphic table function that hides columns from any table
- What a pipelined function needs before it can run in parallel
- Chaining pipelined functions, and the limits of their results
1 diagram · 2 runnable examples (Python, SQL) · about 7 minutes
Opening this part with your pass…
This part opens with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so this part can’t be opened at the moment. The free part above stands on its own. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
Practise
The questions use the schedule and the three-row query:
Practice that adapts to you
Practise this lesson
6 questions about 6 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.
Interview questions
How does a pipelined table function differ from a table function that returns a whole collection? (warm-up; fresher, 2–5 years)
A collection-returning function fills a nested table or varray with every row and returns it when its body ends, so the query waits for the last row and the whole collection sits in memory. A pipelined function hands each row over with PIPE ROW as soon as it is built, so early rows arrive early and memory holds only the rows in flight. When the query needs no more, PIPE ROW raises NO_DATA_NEEDED and the building stops.
Three longer questions follow, on tax lines, an open cursor and hidden columns.
-
Mid to senior
A billing report must turn each of about 2 million monthly invoices into tax lines: one CGST and one SGST line for a sale within a state, one IGST line for a sale across states. A colleague proposes a pipelined function that reads the invoices through a REF CURSOR. Would you build it that way? Defend your choice.
-
Senior to staff
A package declares a cursor over a large table; its pipelined function opens the cursor, pipes transformed rows and closes it after the loop. Reports read the first 20 rows. The second report in a session fails with ORA-06511, cursor already open. Explain the cause, the fix and how you would prove it.
-
Senior to staff
Exports of about 40 tables must leave out every column whose name ends in _PHONE or _EMAIL, and new tables keep arriving. A teammate suggests one hand-written view per table. Would you use views, a table SQL macro or a polymorphic table function? Defend the choice and say what the export query must pass as literals.
Flashcards · 8 cards
Recall it, then check
Say the answer to yourself before you look: recalling it is what makes it stick.
-
What does a pipelined table function gain over one that returns a collection?
It hands each row to the query with PIPE ROW as soon as it is built, so early rows arrive early and the work stops once the query has enough. A collection-returning function builds all 240 rows of a schedule before the query sees month 1.
-
What does a pipelined function's header and return type look like?
RETURN <collection type> PIPELINED, with the collection type declared at schema level or in a package and with SQL element types, never an associative array. PIPE ROW sends one element without giving up control, and the final RETURN carries no value.
-
What happens when the query stops reading a pipelined function early?
The PIPE ROW at which the function is paused raises the predefined exception NO_DATA_NEEDED, ORA-06548, so the function builds no more rows, and the query finishes normally with the rows it has. Unhandled, the exception simply ends the function's run and never reaches the query.
-
When does NO_DATA_NEEDED need a handler?
When the function holds something to release, such as a cursor or a file it opened: the handler closes it and ends with RETURN, and re-raising the exception does the same.
-
Why can a WHEN OTHERS handler break a query that reads only three rows?
It catches NO_DATA_NEEDED too, so WHEN OTHERS THEN RAISE_APPLICATION_ERROR turns the normal early stop into ORA-20001, which looks like a bug in the query. Put WHEN NO_DATA_NEEDED THEN RETURN; before WHEN OTHERS.
-
Why may a pipelined function have built rows the query never read?
The runtime hands piped rows over in batches, so the function can be a few rows past the last one the query read when it stops.
-
What does a table SQL macro do instead?
Its body returns a query as text, and Oracle substitutes that query for the call, so the optimizer reads it as if it had been written into the statement and nothing runs in PL/SQL row by row.
-
Which of the three fits which job?
A pipelined table function for rows PL/SQL computes, such as a schedule or a series; a table SQL macro for a parameterised query whose values already sit in tables; a polymorphic table function, which takes a whole table as an argument, when the result columns are decided per table. PARALLEL_ENABLE lets a parallel query split a pipelined function's input.
Key takeaways
- A pipelined table function hands each row to the query with PIPE ROW as soon as it is built.
- Its return type is a schema-level or package collection type with SQL element types; its RETURN has no value.
- When the query needs no more rows, PIPE ROW raises NO_DATA_NEEDED; a handler is needed only to release resources, and it must come before any OTHERS handler.
- Computed rows suit a pipelined function, rows in tables a table SQL macro, and table-dependent columns a polymorphic table function.
Terms in this lesson
- NO_DATA_NEEDED
- The predefined exception ORA-06548 that PIPE ROW raises in a pipelined table function when the query reading it needs no more rows. The query keeps its rows; a handler is needed only to release resources.
- pipelined table function
- A PL/SQL table function declared PIPELINED that hands each row to the query with PIPE ROW as soon as it is built, instead of building a whole collection and returning it at the end.
- polymorphic table function
- A table function that takes a whole table as an argument and decides its result columns from that table, so one function can serve many different tables.
- table SQL macro
- A PL/SQL function marked SQL_MACRO(TABLE) that returns the text of a query; Oracle uses that query in place of the call in the FROM clause, so no PL/SQL runs for each row.
References
- PL/SQL Language Reference 26ai: Chaining Pipelined Table Functions for Multiple Transformations (Oracle)
- PL/SQL Language Reference 26ai: PIPELINED Clause (Oracle)
- PL/SQL Language Reference 26ai: PIPE ROW Statement (Oracle)
- PL/SQL Language Reference 26ai: Predefined Exceptions (Oracle)
- Database Error Messages: ORA-06548 (Oracle)
- PL/SQL Language Reference 26ai: SQL_MACRO Clause (Oracle)
- SQL Language Reference 26ai: SELECT (Oracle)
- PL/SQL Language Reference 26ai: Overview of Polymorphic Table Functions (Oracle)
- PL/SQL Packages and Types Reference 26ai: DBMS_TF (Oracle)
- PL/SQL Language Reference 26ai: PARALLEL_ENABLE Clause (Oracle)
- Database Licensing Information User Manual 26ai (Oracle)
- PL/SQL Language Reference 26ai: DROP TYPE Statement (Oracle)
- PL/SQL Language Reference 19c: Changes in This Release (Oracle)
Report a problem with this lesson
Kept only in this browser. Your Learn progress