Skip to Main Content

Explore Oracle PL/SQL

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

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Header container

June 04

14:00 UTC   Start Times Around the World

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

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


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.