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 04

14:00 UTC   Start Times Around the World

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

Having trouble watching the video on this page? Open the video in your browser.

Description

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

Your Experts

  • #SELECTION#
    Chris Saxon

    Chris Saxon   

    Chris Saxon is an Oracle Developer Advocate and quizmaster on Dev Gym. 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.
    #MISC#
    #ACTIONS#
  • #SELECTION#
    Steven Feuerstein

    Steven Feuerstein   

    Steven Feuerstein is a Senior Advisor at Insum Solutions (https://insum.ca), and is obsessed with the Oracle PL/SQL language, having written ten books on it (all published by O'Reilly Media). You will find an abundance of quizzes from Steven at the Dev Gym. Enjoy!
    #MISC#
    #ACTIONS#