Polymorphic Table Functions (PTF) hands-on

Preamble

Polymorphic Table Functions (PTF) official definition is:

Polymorphic Table Functions (PTF) are user-defined functions that can be invoked in the FROM clause of a SQL query block. They are capable of processing tables whose row type is not declared at definition time and producing a result table whose row type may or may not be declared at definition time. Polymorphic table functions leverage dynamic SQL capabilities to create powerful and complex custom functions. This is useful for applications demanding an interface with generic extensions which work for arbitrary input tables or queries.

High level Polymorphic Table Functions (PTF) allow you to manipulate result set can be used to remove/add column or rows (easily) from a table. The basic usage is like below example and you obviously need to create pkg_ptf package and ptf function. PTF are an extension of pipelined table:

SELECT * FROM pkg_ptf.func_ptf(TABLE);

Graphically this would be something like:

PTF have been introduced in in 18c and to avoid any potential bugs this blog post has been written using Oracle Database 21c (21.4). My test server is running Redhat 7.9, has 64GB of memory and has 12 cores.

This blog post has few comments as it’s difficult to list the real life use case as even Oracle and non-Oracle developer advocates have huge difficulties to list them. I have also been reluctant to write this post but as I did test the feature I’m just sharing the PL/SQL code mostly as-is. I might come back for update if a need in one of our project arise…

Polymorphic Table Functions (PTF) basic example

The basic example you can do is filtering on-demand few columns from your resultset. First the example given in Oracle official documentation is really complex and to be honest I even do not understand it. For clarify and compactness I have added the PTF function directly inside the package.

The package can obviously be used on any table:

CREATE OR REPLACE PACKAGE pkg_ptf AS
  FUNCTION func_ptf(tab IN TABLE, col IN columns DEFAULT NULL)
  RETURN TABLE pipelined ROW polymorphic USING pkg_ptf;
 
  FUNCTION describe(tab IN OUT dbms_tf.table_t, cols_to_discard IN dbms_tf.columns_t DEFAULT NULL)
  RETURN dbms_tf.describe_t;
END pkg_ptf;
/
 
CREATE OR REPLACE PACKAGE BODY pkg_ptf AS
  FUNCTION describe(tab IN OUT dbms_tf.table_t, cols_to_discard IN dbms_tf.columns_t DEFAULT NULL)
  RETURN dbms_tf.describe_t
  AS
  BEGIN
    FOR i IN 1..tab.COLUMN.COUNT LOOP
      IF tab.COLUMN(i).description.name member OF cols_to_discard THEN
        tab.COLUMN(i).for_read:=FALSE;
        tab.COLUMN(i).pass_through:=FALSE;
      END IF;
    END LOOP;
    RETURN dbms_tf.describe_t();
  END;
END pkg_ptf;
/

The key function is func_ptf that return TABLE PIPELINED ROW POLYMORPHIC type. In this package you can have multiple methods but the only required one is DESCRIBE.

Few queries:

SQL> SET lines 200
SQL> SELECT *
     FROM pkg_ptf.func_ptf(hr.employees)
     FETCH FIRST 5 ROWS only;
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567         17-JUN-03 AD_PRES         24000                                      90
        101 Neena                Kochhar                   NKOCHHAR                  515.123.4568         21-SEP-05 AD_VP           17000                       100            90
        102 Lex                  De Haan                   LDEHAAN                   515.123.4569         13-JAN-01 AD_VP           17000                       100            90
        103 Alexander            Hunold                    AHUNOLD                   590.423.4567         03-JAN-06 IT_PROG          9000                       102            60
        104 Bruce                Ernst                     BERNST                    590.423.4568         21-MAY-07 IT_PROG          6000                       103            60

SQL> SELECT *
     FROM pkg_ptf.func_ptf(hr.employees,columns(salary))
     FETCH FIRST 5 ROWS only;
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID     COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567         17-JUN-03 AD_PRES                                         90
        101 Neena                Kochhar                   NKOCHHAR                  515.123.4568         21-SEP-05 AD_VP                            100            90
        102 Lex                  De Haan                   LDEHAAN                   515.123.4569         13-JAN-01 AD_VP                            100            90
        103 Alexander            Hunold                    AHUNOLD                   590.423.4567         03-JAN-06 IT_PROG                          102            60
        104 Bruce                Ernst                     BERNST                    590.423.4568         21-MAY-07 IT_PROG                          103            60

SQL> SELECT *
     FROM pkg_ptf.func_ptf(hr.employees,columns(salary,phone_number))
     FETCH FIRST 5 ROWS only;
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     HIRE_DATE JOB_ID     COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- --------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     17-JUN-03 AD_PRES                                         90
        101 Neena                Kochhar                   NKOCHHAR                  21-SEP-05 AD_VP                            100            90
        102 Lex                  De Haan                   LDEHAAN                   13-JAN-01 AD_VP                            100            90
        103 Alexander            Hunold                    AHUNOLD                   03-JAN-06 IT_PROG                          102            60
        104 Bruce                Ernst                     BERNST                    21-MAY-07 IT_PROG                          103            60

Remark:
The DESCRIBE function can be overloaded to handle different kind of parameters. For example automatically excluding all BLOB or CLOB columns datatype…

We could also automatically remove the SALARY column from any existing table with this DESCRIBE function:

FUNCTION describe(tab IN OUT dbms_tf.table_t, cols_to_discard IN dbms_tf.columns_t DEFAULT NULL)
RETURN dbms_tf.describe_t
AS
BEGIN
  FOR i IN 1..tab.COLUMN.COUNT LOOP
    IF tab.COLUMN(i).description.name member OF cols_to_discard THEN
      tab.COLUMN(i).for_read:=FALSE;
      tab.COLUMN(i).pass_through:=FALSE;
    END IF;
    IF tab.COLUMN(i).description.name='"SALARY"' THEN
      tab.COLUMN(i).for_read:=FALSE;
      tab.COLUMN(i).pass_through:=FALSE;
    END IF;
  END LOOP;
  RETURN dbms_tf.describe_t();
END;

Remark:
I have fought a bit on this one as dbms_tf.columns_t is a table of dbms_quoted_id and I kept comparing with ‘SALARY’. Needless to say that debugging those PTF is not super simple, see corresponding chapter…

Polymorphic Table Functions (PTF) manipulating output

What if, for example, we would like to mask a column (salary column for example) while using PTF. For this we need to implement an optional new PTF package function call FETCH_ROWS.

From official documentation:

Pass-through columns (pass_through = TRUE) are passed from the input table of the Polymorphic Table Function (PTF) to the output, without any modifications.
Read columns (for_read = TRUE) are a set of table columns that the Polymorphic Table Function (PTF) processes when executing the FETCH_ROWS procedure.

From all the testing I have done it is apparently not possible to modify the value of a column in a row set. To do so you need to remove the column from the original row set and add it back to output row set and make eventually a computation on its original value… Even the 21c official documentation is really poor as lots of type definition are missing. Oracle is also not very verbose in explaining few key concepts…

Remark:
You can have both property set to true. This means that you will have to change column description to avoid having two columns with same header…

ORA-62560: Duplicate column name (SALARY)
62560. 00000 -  "Duplicate column name (%s)"
*Cause:    The new column name already existed as a passthrough column in the
           polymorphic table function.
*Action:   Use a different column name that is unambiguous.

In package (header) I just add fetch_rows that accept no argument:

CREATE OR REPLACE PACKAGE pkg_ptf AS
  FUNCTION func_ptf(tab IN TABLE, cols_to_mask IN columns DEFAULT NULL)
  RETURN TABLE pipelined ROW polymorphic USING pkg_ptf;
 
  FUNCTION describe(tab IN OUT dbms_tf.table_t, cols_to_mask IN dbms_tf.columns_t DEFAULT NULL)
  RETURN dbms_tf.describe_t;
 
  PROCEDURE fetch_rows;
END pkg_ptf;
/

In package body in DESCRIBE function we need now to add the column(s) we plan to modify. In FETCH_ROWS we set the value of each (number) column to a random value between 1 and 100,000:

CREATE OR REPLACE PACKAGE BODY pkg_ptf AS
  FUNCTION describe(tab IN OUT dbms_tf.table_t, cols_to_mask IN dbms_tf.columns_t DEFAULT NULL)
  RETURN dbms_tf.describe_t
  AS
    new_cols dbms_tf.columns_new_t;
    col_id PLS_INTEGER:=1;
  BEGIN
    FOR i IN 1..tab.COLUMN.COUNT LOOP
      IF tab.COLUMN(i).description.name member OF cols_to_mask THEN
        tab.COLUMN(i).for_read:=TRUE;
        tab.COLUMN(i).pass_through:=FALSE;
        --cols(1):= dbms_tf.column_metadata_t(name=>'RAND', type=>dbms_tf.type_number);
        new_cols(col_id) := tab.COLUMN(i).description;
        col_id := col_id + 1;
      END IF;
    END LOOP;
    RETURN dbms_tf.describe_t(new_columns => new_cols);
  END;
 
  PROCEDURE fetch_rows IS
    in_rowset dbms_tf.row_set_t;
    out_rowset dbms_tf.row_set_t;
    rowcount PLS_INTEGER;
    colcount PLS_INTEGER;
    rowindex PLS_INTEGER;
    colindex PLS_INTEGER;
    --col dbms_tf.tab_number_t; 
  BEGIN
    dbms_tf.get_row_set(in_rowset, rowcount, colcount);
    --Iterate all rows of resultset
    FOR rowindex IN 1..rowcount LOOP
      --Iterate all columns of current row
      FOR colindex IN 1..colcount LOOP
        out_rowset(colindex).tab_number(rowindex):=ROUND(dbms_random.VALUE(0,100000));
      END LOOP;
    END LOOP;
    --Put the collection with new values back
    dbms_tf.put_row_set(out_rowset);
  END;
END pkg_ptf;
/

With a “normal” select the SALARY column appears:

SQL> SET lines 200
SQL> SELECT *
     FROM pkg_ptf.func_ptf(hr.employees)
     FETCH FIRST 5 ROWS only;
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567         17-JUN-03 AD_PRES         24000                                      90
        101 Neena                Kochhar                   NKOCHHAR                  515.123.4568         21-SEP-05 AD_VP           17000                       100            90
        102 Lex                  De Haan                   LDEHAAN                   515.123.4569         13-JAN-01 AD_VP           17000                       100            90
        103 Alexander            Hunold                    AHUNOLD                   590.423.4567         03-JAN-06 IT_PROG          9000                       102            60
        104 Bruce                Ernst                     BERNST                    590.423.4568         21-MAY-07 IT_PROG          6000                       103            60

If you decide to mask it:

SQL> SET lines 200
SQL> SELECT *
     FROM pkg_ptf.func_ptf(hr.employees,columns(salary))
     FETCH FIRST 5 ROWS only;
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID     COMMISSION_PCT MANAGER_ID DEPARTMENT_ID     SALARY
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- -------------- ---------- ------------- ----------
        100 Steven               King                      SKING                     515.123.4567         17-JUN-03 AD_PRES                                         90      40499
        101 Neena                Kochhar                   NKOCHHAR                  515.123.4568         21-SEP-05 AD_VP                            100            90      90908
        102 Lex                  De Haan                   LDEHAAN                   515.123.4569         13-JAN-01 AD_VP                            100            90      31369
        103 Alexander            Hunold                    AHUNOLD                   590.423.4567         03-JAN-06 IT_PROG                          102            60      77250
        104 Bruce                Ernst                     BERNST                    590.423.4568         21-MAY-07 IT_PROG                          103            60      82670

Remark:
Of course my masking procedure is far to be perfect as I handle only number columns. You would need to test the type of the column before and apply different masking procedure whether your column is number of character or anything else.

Polymorphic Table Functions (PTF) debugging

The PTF debugging is far to be easy !! Oracle provide a procedure called DBMS_TF.TRACE but again the documentation is really sparse to explain how to use it…

I have tried to implement it in my FETCH_ROWS procedure and it has taken me a while to understand that the principle is the same as DBMS_OUTPUT and so you must set serverouput environment variable to see something appearing…

My new FETCH_ROWS procedure with DBMS_TF.TRACE:

PROCEDURE fetch_rows IS
  in_rowset dbms_tf.row_set_t;
  out_rowset dbms_tf.row_set_t;
  rowcount PLS_INTEGER;
  colcount PLS_INTEGER;
  rowindex PLS_INTEGER;
  colindex PLS_INTEGER;
  --col dbms_tf.tab_number_t; 
BEGIN
  dbms_tf.get_row_set(out_rowset, rowcount, colcount);
  dbms_tf.trace(msg => 'Fetch_rows procedure of pkg_ptf package', with_id => TRUE);
  dbms_tf.get_row_set(in_rowset, rowcount, colcount);
  dbms_tf.trace(msg=>'Input rowset');
  dbms_tf.trace(in_rowset);
  --Iterate all rows of resultset
  FOR rowindex IN 1..rowcount LOOP
    --Iterate all columns of current row
    FOR colindex IN 1..colcount LOOP
      out_rowset(colindex).tab_number(rowindex):=ROUND(dbms_random.VALUE(0,100000));
    END LOOP;
  END LOOP;
  --Put the collection with values back
  dbms_tf.trace(msg=>'Output rowset');
  dbms_tf.trace(out_rowset);
  dbms_tf.put_row_set(out_rowset);
  dbms_tf.trace(msg=>'Environment');
  dbms_tf.trace(dbms_tf.get_env);
END;

Testing:

SQL> SET serveroutput ON lines 200
SQL> SELECT * FROM pkg_ptf.func_ptf(hr.employees,columns(salary)) WHERE employee_id=100;
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID     COMMISSION_PCT MANAGER_ID DEPARTMENT_ID     SALARY
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- -------------- ---------- ------------- ----------
        100 Steven               King                      SKING                     515.123.4567         17-JUN-03 AD_PRES                                         90      80329
 
Fetch_rows PROCEDURE OF pkg_ptf pac<id=000000000000000C>
Input rowset
.... [1] = {"SALARY":24000}
Output rowset
.... [1] = {"SALARY":80329}
Environment
 
 
 
 
CURRENT Parallel Environment:
....Instance id:..........65535
....SESSION id:...........262143
....Slave Server Grouup:..65535
....Slave SET NUMBER:.....65535
....No OF Sibling Slaves:.-1
....Global Slave NUMBER:..-1
....No OF local Slaves:...-1
....Local Slave No:.......-1
 
 
....Get Columns:
 
 
 
 
........get COLUMN[1] =
__________Type:...............NUMBER
__________Max LENGTH:.........22
__________Name:..............."SALARY"
__________Name LENGTH:........8
__________Precision:..........8
__________Scale:..............2
 
 
 
 
 
 
....Put Columns:
 
 
........put COLUMN[1] =
__________Type:...............NUMBER
__________Max LENGTH:.........22
__________Name:..............."SALARY"
__________Name LENGTH:........8
__________Precision:..........8
__________Scale:..............2
 
 
 
 
 
 
....Referenced Columns:
 
 
........ COLUMN[1] = "SALARY" referenced
 
 
 
 
 
 
.... This IS USER query
 
 
....Numbers OF ROWS IN this ROW SET: 1

I also had strange behavior with SALARY column not more set by my FETCH_ROWS function while playing with DBMS_TF.TRACE. I even had to remove all trace, recompile the package to restart from a clean situation.

IT World

Polymorphic Table Functions (PTF) hands-on

Table of contents

Preamble

Polymorphic Table Functions (PTF) basic example

Polymorphic Table Functions (PTF) manipulating output

Polymorphic Table Functions (PTF) debugging

References

About Post Author

Yannick Jaquier

Related Posts:

Leave a Reply Cancel Reply