Returning result sets from PL/SQL functions

Pipelined table functions let you write PL/SQL functions that return rows one at a time, which can then be queried with SELECT just like a regular table. They're perfect for data transformations, generating test data, or wrapping complex logic in a queryable interface.

Recipe #1 - Basic pipelined function

First, create a type for the rows you want to return:

CREATE OR REPLACE TYPE t_num_row AS OBJECT (
  id    NUMBER,
  value NUMBER
);

CREATE OR REPLACE TYPE t_num_table AS TABLE OF t_num_row;

Then create the pipelined function:

CREATE OR REPLACE FUNCTION generate_numbers(p_count NUMBER)
  RETURN t_num_table PIPELINED
AS
BEGIN
  FOR i IN 1..p_count LOOP
    PIPE ROW(t_num_row(i, DBMS_RANDOM.VALUE(1, 100)));
  END LOOP;
  RETURN;
END;

Now you can query it like a table:

SELECT * FROM TABLE(generate_numbers(10)) ORDER BY value DESC;

Recipe #2 - Transforming data with a pipelined function

A practical example that splits a delimited string into rows:

CREATE OR REPLACE TYPE t_varchar_row AS OBJECT (item VARCHAR2(4000));
CREATE OR REPLACE TYPE t_varchar_table AS TABLE OF t_varchar_row;

CREATE OR REPLACE FUNCTION split_string(
  p_string    VARCHAR2,
  p_delimiter VARCHAR2 DEFAULT ','
) RETURN t_varchar_table PIPELINED
AS
  l_str  VARCHAR2(32767) := p_string;
  l_pos  NUMBER;
BEGIN
  LOOP
    l_pos := INSTR(l_str, p_delimiter);
    IF l_pos > 0 THEN
      PIPE ROW(t_varchar_row(TRIM(SUBSTR(l_str, 1, l_pos - 1))));
      l_str := SUBSTR(l_str, l_pos + LENGTH(p_delimiter));
    ELSE
      PIPE ROW(t_varchar_row(TRIM(l_str)));
      EXIT;
    END IF;
  END LOOP;
  RETURN;
END;

Usage:

SELECT * FROM TABLE(split_string('Oracle,MySQL,PostgreSQL,SQLite'));

The key advantage of pipelined functions is that calling code starts receiving rows immediately — the function doesn't have to finish generating all rows before the first one is returned. This makes them very memory-efficient for large result sets.