A polymorphic table function, PTF, takes a table and returns rows whose columns depend on its input, so one function works on any table. It is written as a package with a DESCRIBE function, which decides the output columns when the query is parsed, and a FETCH_ROWS procedure, which computes them as rows flow through, using DBMS_TF.
Code for This Guide
The main examples are in the examples/pkg-generic-types folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create package p as
function describe (tab in out dbms_tf.table_t, cols in dbms_tf.columns_t)
return dbms_tf.describe_t;
procedure fetch_rows;
end;
create function f (tab table, cols columns)
return table pipelined row polymorphic using p;
select * from f(some_table, columns(col1, col2));In DESCRIBE, PASS_THROUGH decides which input columns appear in the output and FOR_READ which ones FETCH_ROWS reads. FETCH_ROWS reads with GET_ROW_SET and writes new columns with PUT_COL.
Mask Any Columns of Any Table
MASK_COLS hides the columns it is given and adds a masked copy of each, where every letter and digit from the third character on becomes an asterisk.
Example:
create or replace package mask_ptf as
function describe (tab in out dbms_tf.table_t, cols in dbms_tf.columns_t)
return dbms_tf.describe_t;
procedure fetch_rows;
end;
/
create or replace package body mask_ptf as
function describe (tab in out dbms_tf.table_t, cols in dbms_tf.columns_t)
return dbms_tf.describe_t is
v_new dbms_tf.columns_new_t;
begin
for i in 1 .. tab.column.count loop
if tab.column(i).description.name member of cols then
tab.column(i).pass_through := false; -- the original column is hidden
tab.column(i).for_read := true; -- but read by FETCH_ROWS
v_new(v_new.count + 1) := dbms_tf.column_metadata_t(
name => trim(both '"' from tab.column(i).description.name) || '_MASKED',
type => dbms_tf.type_varchar2);
end if;
end loop;
return dbms_tf.describe_t(new_columns => v_new);
end;
procedure fetch_rows is
v_rows dbms_tf.row_set_t;
v_count pls_integer;
v_out dbms_tf.tab_varchar2_t;
begin
dbms_tf.get_row_set(v_rows, v_count);
for c in 1 .. v_rows.count loop -- each column read, in order
for r in 1 .. v_count loop
v_out(r) := regexp_replace(v_rows(c).tab_varchar2(r), '[[:alnum:]]', '*', 3);
end loop;
dbms_tf.put_col(c, v_out);
end loop;
end;
end;
/
create or replace function mask_cols (tab table, cols columns)
return table pipelined row polymorphic using mask_ptf;
/
select customer_id, email_masked, phone_masked
from mask_cols(customers, columns(email, phone))
where customer_id <= 3;Output:
Package MASK_PTF compiled
Package Body MASK_PTF compiled
Function MASK_COLS compiled
CUSTOMER_ID EMAIL_MASKED PHONE_MASKED
______________ ____________________________ ___________________
1 di***.*****@*******.*** +9** ** *** ****
2 ra**.******@*******.*** +9* ** *** ****
3 lu***.*******@*******.*** +4* ** *** ****Called on CUSTOMERS with EMAIL and PHONE, it returns EMAIL_MASKED and PHONE_MASKED instead of the originals, while CUSTOMER_ID passes through. The same function would mask any columns of any table. The example drops the function and the package afterward.
Things to Know
- ROW polymorphic functions see one row at a time; TABLE polymorphic functions can see partitions of rows, with PARTITION BY and ORDER BY in the call.
- DESCRIBE runs once per parse, FETCH_ROWS once per batch of rows.
- When a function only needs to rewrite a query, a table SQL macro is simpler.
Related Guides
Conclusion
Polymorphic table functions take any table and return rows with columns decided at parse time. Write DESCRIBE to choose the columns and FETCH_ROWS to compute them with DBMS_TF, and call the function on any table with COLUMNS for its arguments.
