Skip to Main Content

Explore Oracle PL/SQL

Free tips and training every month! Subscribe for reminders and more from Office Hours. FAQ

Header container

June 4, 2019

Polymorphic Table Functions
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

Subscribe to give us feedback and be notified of upcoming sessions!

Ask a Question

Ask The Experts - Right Now!

Do you have a question about Polymorphic Table Functions. you'd like our experts to answer in their next session? Sign in and submit it here.

Please note that we cannot guarantee to answer all questions. We cannot help you with open Service Requests or account/licensing issues.

Experts

Your Experts
Chris Saxon
Chris Saxon, Oracle Developer Advocate for SQL    
Chris Saxon is the quizmaster for the Database Design competitions and an Oracle Developer Advocate for SQL. His job is to help you get the best out of the Oracle Database and have fun with SQL! He's also part of the answer team on Ask TOM and creates YouTube videos at The Magic of SQL channel.
Steven Feuerstein
Steven Feuerstein, Developer Advocate for PL/SQL    
Steven Feuerstein leads a team of Developer Advocate at Oracle Corporation, and is obsessed with the Oracle PL/SQL language, having written ten books on it (all published by O'Reilly Media). He's published over 1400 quizzes on PL/SQL at the Oracle Dev Gym.