Well, that’s a mouthful, isn’t it?
Polymorphic table functions were added to the Oracle Database in 18.1.
Tim Hall of ORACLE-BASE sums it up nicely:
The return type of a Polymorphic Table Function (PTF) can be determined by input parameters. This differs from conventional table functions, where the output table type is fixed at compile time.
(my italics)
And Chris Saxon, who joined us November 10 on Feuertips to talk about PTFs, offers these thoughts about use cases:
- Read a CSV file that has an unknown number of fields and split the values out into separate columns without having to write some really complex SQL
- Dynamic PIVOT and UNPIVOT operations
- Top-N Plus queries. (Use PTFs instead of a traditional Top-N query, but with an extra summary row, giving totals for all the remaining rows in the dataset.)
- Executing R scripts against a table, returning the results of the R script
He also noted in the session that for most use cases of PTFs, you will probably be better off using SQL Macros!
See the full Feuertips episode here
Download the scripts Chris used for this episode from LiveSQL.
Here are some links to resources on PTFs:
Overview of Polymorphic Table Functions – Oracle Documentation
How to Dynamically Change the Columns in a SQL Query – Chris Saxon
A Presentation on PTFs – Chris Saxon
Polymorphic Table Functions in Oracle Database 18c – Tim Hall
Oracle LiveSQL Examples – Chris Saxon
