Pipelined/Polymorphic Table Functions and Advanced SQL/PLSQL Integration Concepts

Learning outcomes

ServiceHub needs to expose a procedurally transformed row stream to SQL and later considers a generic transformation whose output columns depend on the input table. A normal view or SQL expression should remain the first choice. When procedural transformation is genuinely required, Oracle offers classic table functions, pipelining, and polymorphic table functions (PTFs) with very different compile/runtime contracts.

1. A table function makes program output queryable

A table function returns a SQL collection that can appear in a query's FROM clause. The caller can then filter, join, aggregate, and project rows produced by procedural code. This is useful for transformations that are awkward to express directly in relational SQL, but it adds a PL/SQL execution boundary and a user-defined cardinality/cost challenge.

CREATE OR REPLACE TYPE servicehub_case_row_t AS OBJECT(  case_id NUMBER,  normalized_status VARCHAR2(12),  amount NUMBER);/CREATE OR REPLACE TYPE servicehub_case_row_tab_tAS TABLE OF servicehub_case_row_t;/

2. PIPELINED streams rows as they are produced

A nonpipelined collection function normally constructs its result collection before returning it. A pipelined function can call PIPE ROW repeatedly and make rows available to the SQL consumer incrementally. This can improve first-row latency and reduce memory needed to materialize the entire function result.

CREATE OR REPLACE FUNCTION servicehub_pipe_cases(  p_min_amount IN NUMBER) RETURN servicehub_case_row_tab_tPIPELINEDAUTHID DEFINERISBEGIN  FOR r IN (    SELECT case_id,status_code,amount    FROM servicehub_tf_case    WHERE amount >= p_min_amount    ORDER BY case_id  ) LOOP    PIPE ROW(      servicehub_case_row_t(        r.case_id,        UPPER(r.status_code),        r.amount      )    );  END LOOP;  RETURN;END;/

SELECT p.case_id,p.normalized_status,p.amountFROM TABLE(servicehub_pipe_cases(150)) pWHERE p.normalized_status <> 'CLOSED'ORDER BY p.case_id;

The outer SQL remains declarative, but the transformation inside the function is procedural. If the same result can be expressed as a simple view or SQL expression, that usually gives the optimizer more direct visibility and fewer moving parts.

3. Early consumer termination is a normal pipeline event

If a consumer stops requesting rows—for example because a row-limiting operation is satisfied—Oracle can raise the internal NO_DATA_NEEDED exception inside the pipelined function so it can stop producing. A blanket WHEN OTHERS that converts every exception to an application error can accidentally turn this normal control flow into a failure.

EXCEPTION  WHEN NO_DATA_NEEDED THEN    -- Perform only necessary local cleanup, then let normal early-stop semantics stand.    RETURN;  WHEN OTHERS THEN    -- Capture diagnostics/cleanup and preserve real failures.    RAISE;END;

Do not add exception handlers if the function has no resources to clean up; simpler code is often safer.

4. Parallel table-function declarations are not a Free performance benchmark

Classic table functions can be declared PARALLEL_ENABLE with partitioning semantics for suitable REF CURSOR inputs. Parallel execution adds data redistribution and worker topology. Oracle AI Database Free is limited to two foreground CPU cores, so this chapter does not claim enterprise throughput gains from a local parallel lab.

Also note that a table function with autonomous-transaction DML has special PIPE ROW commit/rollback restrictions. Avoid side-effecting table functions unless the transaction semantics are essential and thoroughly tested.

5. Polymorphic table functions determine output shape from input arguments

A polymorphic table function (PTF) can accept a table argument and determine its returned columns from that argument. The implementation package uses DBMS_TF. DESCRIBE runs during SQL cursor compilation and is required; optional OPEN, FETCH_ROWS, and CLOSE methods run during execution.

CREATE OR REPLACE PACKAGE sh12_rownum_ptf AS  FUNCTION describe(    tab IN OUT DBMS_TF.TABLE_T  ) RETURN DBMS_TF.DESCRIBE_T;  PROCEDURE fetch_rows;END sh12_rownum_ptf;/CREATE OR REPLACE PACKAGE BODY sh12_rownum_ptf AS  FUNCTION describe(    tab IN OUT DBMS_TF.TABLE_T  ) RETURN DBMS_TF.DESCRIBE_T IS  BEGIN    RETURN DBMS_TF.DESCRIBE_T(      new_columns => DBMS_TF.COLUMNS_NEW_T(        1 => DBMS_TF.COLUMN_METADATA_T(               name => 'ROW_ID',               type => DBMS_TF.TYPE_NUMBER             )      )    );  END;  PROCEDURE fetch_rows IS    l_count PLS_INTEGER := DBMS_TF.GET_ENV().ROW_COUNT;    l_start NUMBER := 1;    l_col   DBMS_TF.TAB_NUMBER_T;  BEGIN    DBMS_TF.XSTORE_GET('rid',l_start);    FOR i IN 1..l_count LOOP      l_col(i) := l_start + i - 1;    END LOOP;    DBMS_TF.PUT_COL(1,l_col);    DBMS_TF.XSTORE_SET('rid',l_start+l_count);  END;END sh12_rownum_ptf;/

CREATE OR REPLACE FUNCTION sh12_add_rownum(tab TABLE)RETURN TABLEPIPELINED ROW POLYMORPHICUSING sh12_rownum_ptf;/SELECT *FROM sh12_add_rownum(servicehub_tf_case)ORDER BY case_id;

Current PL/SQL syntax restricts PTF declarations: they cannot specify AUTHID, DETERMINISTIC, RESULT_CACHE, or PARALLEL_ENABLE. The DBMS_TF package itself executes with invoker-rights privileges. Treat this as a compile-time/runtime transformation framework, not as an ordinary package function.

6. Choose the least powerful abstraction that preserves the SQL model

A PTF that only renames one fixed column is usually needless abstraction. A view or macro is easier to reason about. Use PTFs for genuinely shape-polymorphic transformations.

7. Reproducible mandatory pipeline lab

BEGIN EXECUTE IMMEDIATE 'DROP TABLE servicehub_tf_case PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/CREATE TABLE servicehub_tf_case(  case_id NUMBER PRIMARY KEY,  status_code VARCHAR2(12) NOT NULL,  amount NUMBER(10,2) NOT NULL);INSERT INTO servicehub_tf_case VALUES(1,'open',100);INSERT INTO servicehub_tf_case VALUES(2,'hold',200);INSERT INTO servicehub_tf_case VALUES(3,'closed',300);COMMIT;-- Create SERVICEHUB_CASE_ROW_T, SERVICEHUB_CASE_ROW_TAB_T,-- and SERVICEHUB_PIPE_CASES from sections 1–2.

SELECT *FROM TABLE(servicehub_pipe_cases(150))ORDER BY case_id;EXPLAIN PLAN SET STATEMENT_ID='SH12_L4_PIPE'FORSELECT * FROM TABLE(servicehub_pipe_cases(150));SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,'SH12_L4_PIPE','BASIC +PREDICATE'));

BEGIN EXECUTE IMMEDIATE 'DROP FUNCTION sh12_add_rownum'; EXCEPTION WHEN OTHERS THEN NULL; END;/BEGIN EXECUTE IMMEDIATE 'DROP PACKAGE sh12_rownum_ptf'; EXCEPTION WHEN OTHERS THEN NULL; END;/BEGIN EXECUTE IMMEDIATE 'DROP FUNCTION servicehub_pipe_cases'; EXCEPTION WHEN OTHERS THEN NULL; END;/BEGIN EXECUTE IMMEDIATE 'DROP TYPE servicehub_case_row_tab_t'; EXCEPTION WHEN OTHERS THEN NULL; END;/BEGIN EXECUTE IMMEDIATE 'DROP TYPE servicehub_case_row_t'; EXCEPTION WHEN OTHERS THEN NULL; END;/DROP TABLE servicehub_tf_case PURGE;

8. Production judgment

Prefer ordinary SQL/view/macros when the transformation is relational and fixed-shape. Use pipelining when procedural streaming materially improves the design and PTFs only when input-driven shape polymorphism is the actual requirement. Measure cardinality, buffers, latency, PGA, and optimizer behavior on representative data; do not assume procedural streaming is faster.

No option, pack, restart, or COMPATIBLE change is required for the mandatory pipelined function. Lesson 5 finishes the chapter by turning these PL/SQL capabilities into a governed application API with least privilege and explicit transaction/deployment ownership.

Authoritative references

• Pipelined Table Functions — table functions, pipelining, PIPE ROW and NO_DATA_NEEDED
• PIPELINED Clause — classic and polymorphic table-function syntax/restrictions
• DBMS_TF — PTF execution model, security and examples
• Using Pipelined and Parallel Table Functions — streaming/parallel data flow and interface model
• SQL_MACRO Clause — comparison with reusable SQL-generating functions