Polymorphic Table Functions (PTF) - introduced in Oracle Database 18c - 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.

In this session, Chris & Steven give an overview how to create PTFs.

Highlights include:

- 00:35 What are PTFs and why would you use them?
- 04:55 An overview of how you create the package and procedure for a PTF
- 09:50 An example describe function to add new columns to the output
- 11:50 An example fetch_rows procedure to assign new values to columns added by the describe
- 18:30 Defining which order you process values from the input table with partition by & order by clauses
- 27:40 PTFs rows in batches; why, what this means, and how to overcome this by saving execution state
- 30:30 Managing PTF execution state with the dbms_tf.xtore* procedures
- 36:25 How to debug PTFs; using the dbms_tf.trace procedure
- 40:00 LiveSQL examples of PTF implementations, including: CSV -> columns conversion; dynamic pivot
- 46:25 User-defined aggregation functions vs PTFs

Slides available at https://www.slideshare.net/ChrisSaxon1/polymorphic-table-functions-in-sql