Explain pipelined table functions in PL/SQL

How it works

A pipelined table function is declared to RETURN a collection type but streams rows back to the caller one at a time via PIPE ROW, as they're produced, instead of building the entire collection in memory first. The caller then queries it with TABLE(function_call(...)) as if it were a real table. This suits row-by-row transformation logic where materializing the full result set upfront would be wasteful, letting Oracle pipeline rows to the consuming query as they're generated.

CREATE OR REPLACE TYPE t_number_row AS OBJECT (val NUMBER);
/
CREATE OR REPLACE TYPE t_number_tab AS TABLE OF t_number_row;
/

CREATE OR REPLACE FUNCTION squares_up_to(p_max NUMBER) RETURN t_number_tab PIPELINED IS
BEGIN
  FOR v_i IN 1..p_max LOOP
    PIPE ROW (t_number_row(v_i * v_i));
  END LOOP;
  RETURN;
END;
/

SELECT * FROM TABLE(squares_up_to(5));

Related Questions