DEV Community

SANDEEP KUMAR
SANDEEP KUMAR

Posted on

Oracle PL/SQL: Regular vs. Pipelined Table Functions

#plsql #oracle

Table functions are specialized PL/SQL functions that return collections of rows and can be queried directly in the FROM clause of a SQL query as if they were physical database tables.

Key Comparison: Regular vs. Pipelined Table Functions

Feature Regular Table Function Pipelined Table Function
Memory Allocation High (buffers entire collection in PGA memory) Low (streams rows iteratively without holding the entire result set in memory)
Response Time High Time to First Row (TTFR) — caller waits until processing completes Low Time to First Row (TTFR) — caller gets rows immediately as they are generated
Syntax Keyword Standard RETURN <collection_type> Requires PIPELINED keyword in function header
Row Emission Standard assignment into collection Uses PIPE ROW (...) construct
Return Statement RETURN collection_variable; RETURN; (empty return statement)
Best Use Case Small data sets, lookup operations Large ETL operations, data transformations, real-time streaming

Database Prerequisites (Object & Table Types)

To query a function using SQL, you must first define named SQL object types at the database level.

-- 1. Create Row Type Object
CREATE OR REPLACE TYPE t_tf_row AS OBJECT (
    id          NUMBER,
    description VARCHAR2(50)
);
/

-- 2. Create Nested Table Type based on the Row Object
CREATE OR REPLACE TYPE t_tf_tab AS TABLE OF t_tf_row;
/
Enter fullscreen mode Exit fullscreen mode

Implementation 1: Regular Table Function

Regular table functions fully populate a collection in memory before returning the entire dataset to the caller.

CREATE OR REPLACE FUNCTION get_tab_tf (
    p_rows IN NUMBER
) RETURN t_tf_tab AS
    l_tab t_tf_tab := t_tf_tab();
BEGIN
    FOR i IN 1..p_rows LOOP
        l_tab.EXTEND;
        l_tab(l_tab.LAST) := t_tf_row(i, 'Description for ' || i);
    END LOOP;

    RETURN l_tab;
END get_tab_tf;
/
Enter fullscreen mode Exit fullscreen mode

Testing the Regular Function:

SELECT * FROM TABLE(get_tab_tf(10));
Enter fullscreen mode Exit fullscreen mode

Implementation 2: Pipelined Table Function

Pipelined table functions stream data back to the calling query immediately upon creation using PIPE ROW.

CREATE OR REPLACE FUNCTION get_tab_ptf (
    p_rows IN NUMBER
) RETURN t_tf_tab PIPELINED AS
BEGIN
    FOR i IN 1..p_rows LOOP
        -- Sends individual row directly to the caller
        PIPE ROW (t_tf_row(i, 'Description for ' || i));
    END LOOP;

    -- Empty RETURN transfers execution control back to the caller
    RETURN;
END get_tab_ptf;
/
Enter fullscreen mode Exit fullscreen mode

Testing the Pipelined Function:

SELECT * FROM TABLE(get_tab_ptf(10));
Enter fullscreen mode Exit fullscreen mode

Important Technical & Interview Notes

  • IMPORTANT: In a pipelined function, attempting to execute RETURN collection_name; results in a compilation error (PLS-00633: RETURN statement in a pipelined function cannot take an expression). The RETURN; statement must remain empty.
  • NOTE: Pipelining substantially reduces overall system memory usage and drastically decreases "Time to First Row" for large datasets.
  • NOTE: Parallel-enabled table functions allow workload splitting across multiple parallel slave processes (PARALLEL_ENABLE clause), accelerating execution during high-volume processing.
  • IMPORTANT: The TABLE() clause wrapper around a table function in SQL queries is mandatory in Oracle 11g and earlier, but became optional starting from Oracle Database 12c Release 2 (12.2). 
-- Valid in Oracle 12c R2 and later without the TABLE() operator:
SELECT * FROM get_tab_ptf(10);
Enter fullscreen mode Exit fullscreen mode

Interview Questions & Answers

Q1: What is the primary operational difference between a Regular and Pipelined Table Function?

  • Answer: A regular table function constructs the entire result set in Process Global Area (PGA) memory before returning control to the caller. A pipelined table function sends rows back incrementally as they are processed using PIPE ROW, saving PGA memory and significantly reducing the time required to display initial results.

Q2: What happens if you specify a variable inside the RETURN statement of a pipelined function?

  • Answer: Oracle generates a PL/SQL compilation error (PLS-00633). Pipelined table functions pass data back using PIPE ROW(...), so the final RETURN; statement must be blank to signal execution completion.

Top comments (0)