Polymorphic Table Functions – Introduction

Polymorphic Table Functions (PTF’s) work with input that is not predefined and provide output that is not predefined. Thus they are very flexible. I encourage you to Google the phrase “Oracle Polymorphic Table Functions” to find information on this feature introduced in Oracle 18c.

in this series of posts I’ll work through some examples of what PTF’s can and cannot do. Along the way I’ll explain concepts and constraints you need to understand to make things work.

I’ll start at the beginning of any query – the parse – which is the first thing Oracle does when executing a query. Parsing is extremely complex so for the purposes of this post I’ll limit the discussion to one factor – evaluating the object being queried. For example how does the PTF shown below get information about the structure of table1?

SELECT col1
  FROM poly_func(table1);

PTF’s accept the object being queried as a parameter. Oracle could simply parse that. But PTF’s allow developers to interrogate and influence the parse usingĀ  a DESCRIBE function. The code snippet below defines a package with a simple DESCRIBE function augmented to show the objects columns.

CREATE OR REPLACE PACKAGE poly_pkg AS
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
  RETURN DBMS_TF.DESCRIBE_T;
END;

CREATE OR REPLACE PACKAGE BODY poly_pkg AS
  FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
           RETURN DBMS_TF.DESCRIBE_T AS
  BEGIN
    -- Display all of the columns in tab
    FOR counter IN 1..p_tbl.column.count LOOP
       DBMS_OUTPUT.PUT_LINE('Column ' || 
             tab.column(counter).description.name);
    END LOOP;
    RETURN NULL;
  END;
END;

I’ll discuss details like the TABLE_T type in later posts.

Next I’ll define the PTF and associate it with the package.

CREATE OR REPLACE FUNCTION poly_func(p_tbl IN TABLE)
  RETURN TABLE PIPELINED
  ROW POLYMORPHIC USING poly_pkg;

I now have all that I need to query the PTF except for data. I’ll take care of that now.

CREATE TABLE table1
( col1 NUMBER,
  col2 NUMBER );
INSERT INTO table1
VALUES(19,99);

Next I’ll query the PTF with the table I just created. During the parse the DESCRIBE function is executed showing the column names.

SELECT col1
  FROM poly_func(table1);
COL1
----
  19
Column "COL1"
Column "COL2"

Note that all columns in table1 are listed even though I only queried one. The DESCRIBE function sees the whole object – not just what is being queried.

Even if the query returns no records the parse still occurs.

SELECT col1
  FROM poly_func(table1)
 WHERE 1 = 2;
no rows selected
Column "COL1"
Column "COL2"

Even failed queries require a parse…and a call to the DESCRIBE function.

SELECT notthere
  FROM poly_func(table1);
SELECT notthere
*
ERROR at line 1:
ORA-00904: "NOTTHERE": invalid identifier
Column "COL1"
Column "COL2"

Now I’ll create a view and query it. The DESCRIBE function only sees the columns in the view.

CREATE VIEW view1 AS
SELECT col2
  FROM table1;
SELECT *
  FROM poly_func(view1);
COL2
----
  99
Column "COL2"

PTF’s can also handle Common Table Expression (aka the WITH clause). The DESCRIBE function sees the columns in the sub-query.

WITH t1 AS
( SELECT col2 AS c2
    FROM table1 )
SELECT *
 FROM poly_func(t1);
C2
--
99
Column "C2"

Thanks for reading any I hope you enjoy this series!