The Memory Exhaustion Problem#

Imagine you need to write a PL/SQL function that parses a complex 5GB text file, validates the data, and returns it to a SQL INSERT statement.

If you use a standard PL/SQL function that returns a Collection (like a nested table), the function must parse the entire 5GB file, load all 10 million rows into a collection variable in the PGA (Program Global Area) memory, and only then return it to the SQL engine.

This will almost certainly result in an ORA-04030: out of process memory crash.

Pipelined Functions fix this. Instead of returning the entire collection at once, a pipelined function yields (pipes) one row at a time. The SQL engine consumes that row immediately and discards it from memory, waiting for the next row. It is a true data stream.

Step 1: Creating SQL Object Types#

Because pipelined functions return data that SQL can query, the return type must be known to the SQL engine (you cannot use PL/SQL-only types defined in a package spec).

First, create an Object Type to represent a single row, and then a Nested Table Type to represent the collection.

CREATE OR REPLACE TYPE tx_row_type AS OBJECT (
  tx_id    NUMBER,
  amount   NUMBER,
  status   VARCHAR2(20)
);
/

-- 2. The Collection structure (Table of Rows)
CREATE OR REPLACE TYPE tx_table_type AS TABLE OF tx_row_type;
/

Step 2: Writing the Pipelined Function#

To make a function pipelined, you add the PIPELINED keyword. Inside the loop, instead of adding data to a collection variable, you use the PIPE ROW() command.

Finally, a pipelined function ends with a naked RETURN; statement (you do not return a variable).

CREATE OR REPLACE FUNCTION generate_dummy_tx(p_count NUMBER)
  RETURN tx_table_type PIPELINED 
IS
  v_row tx_row_type := tx_row_type(NULL, NULL, NULL);
BEGIN
  FOR i IN 1..p_count LOOP
    -- Populate the object
    v_row.tx_id := i;
    v_row.amount := ROUND(DBMS_RANDOM.VALUE(10, 1000), 2);
    
    IF MOD(i, 2) = 0 THEN
      v_row.status := 'APPROVED';
    ELSE
      v_row.status := 'PENDING';
    END IF;
    
    -- Stream the row back to the caller IMMEDIATELY
    PIPE ROW(v_row);
  END LOOP;
  
  -- Signal that the stream is complete
  RETURN; 
END;
/

Step 3: Querying with TABLE()#

You cannot call a pipelined function like a normal PL/SQL function. You must query it inside a SELECT statement, wrapped in the TABLE() operator.

SELECT * 
FROM TABLE(generate_dummy_tx(500000))
WHERE status = 'APPROVED';

Because it streams, the first few rows will appear in your SQL client instantly, even if you asked for 500,000 rows. The memory overhead remains near zero throughout the entire execution.

Common Gotchas#

Key Takeaways#