Polymorphic Table Functions in Oracle 18c and Beyond

Functions That Adapt to Their Input

Traditional table functions have a fixed return type. You define the shape of the output row at compile time, and every invocation returns rows with the same columns. Polymorphic table functions (PTFs), introduced in Oracle 18c, break this limitation. A PTF can examine its input at runtime and dynamically define its output columns. The output shape can vary depending on what table or query you pass in.

Two Flavors: Row and Table Semantics

PTFs come in two varieties. Row semantics PTFs process one row at a time and can add, remove, or modify columns. Table semantics PTFs receive the entire input row set and can perform operations that span multiple rows, similar to aggregate functions.

A Practical Row Semantics Example: Column Masking

Imagine you need a function that takes any query result and masks all columns that contain sensitive data. The function does not know in advance which columns exist or which ones are sensitive. Here is a simplified version:

-- The implementation package
CREATE OR REPLACE PACKAGE ptf_mask_pkg AS

   -- Describe function: called at compile time
   FUNCTION describe(
      tab IN OUT DBMS_TF.TABLE_T
   )
   RETURN DBMS_TF.DESCRIBE_T;

   -- Fetch_rows: called at runtime
   PROCEDURE fetch_rows;

END ptf_mask_pkg;
/

CREATE OR REPLACE PACKAGE BODY ptf_mask_pkg AS

   FUNCTION describe(tab IN OUT DBMS_TF.TABLE_T)
   RETURN DBMS_TF.DESCRIBE_T
   IS
   BEGIN
      -- Mark columns containing 'SSN', 'SALARY', or 'EMAIL' for read/write
      FOR i IN 1 .. tab.column.COUNT LOOP
         IF UPPER(tab.column(i).description.name) LIKE '%SSN%'
            OR UPPER(tab.column(i).description.name) LIKE '%SALARY%'
            OR UPPER(tab.column(i).description.name) LIKE '%EMAIL%'
         THEN
            tab.column(i).for_read  := TRUE;
            tab.column(i).pass_through := FALSE;
         END IF;
      END LOOP;
      RETURN NULL;
   END describe;

   PROCEDURE fetch_rows IS
      l_rowset DBMS_TF.ROW_SET_T;
      l_rowcount PLS_INTEGER;
   BEGIN
      DBMS_TF.GET_ROW_SET(l_rowset, l_rowcount);
      FOR col_idx IN 1 .. l_rowset.COUNT LOOP
         FOR row_idx IN 1 .. l_rowcount LOOP
            l_rowset(col_idx).tab_varchar2(row_idx) := '***MASKED***';
         END LOOP;
      END LOOP;
      DBMS_TF.PUT_ROW_SET(l_rowset);
   END fetch_rows;

END ptf_mask_pkg;
/

-- Register the PTF
CREATE OR REPLACE FUNCTION mask_sensitive(
   tab TABLE
)
RETURN TABLE PIPELINED ROW POLYMORPHIC USING ptf_mask_pkg;
/

-- Usage: works with ANY table
SELECT * FROM mask_sensitive(employees);
SELECT * FROM mask_sensitive(customers);

When PTFs Shine

Polymorphic table functions are ideal for building reusable data transformation utilities that work across many different tables. Data masking, column pivoting, data quality checks, and universal audit trail functions are all excellent use cases. They let you write the logic once and apply it to any table structure.

Version Progression

Oracle 18c introduced the base PTF framework. Oracle 19c fixed several bugs related to PTF behavior with complex data types and improved the optimizer’s handling of PTFs in execution plans. Oracle 21c expanded the DBMS_TF package with additional capabilities, including better support for LOB columns and improved error messages for common implementation mistakes. In 23ai, PTFs have been further optimized for parallel execution, making them viable for large data processing scenarios.

PTFs represent a significant shift in how you can think about data transformation in Oracle. They are worth learning even if you do not have an immediate use case, because once you understand them, you will start recognizing opportunities to apply them across your codebase.