This tutorial takes you step by step through the basics of non-leaf Polymorphic Table Functions (PTF). Before starting the lessons though it is important to understand what PTFs are, what they can do, and what they cannot do.
There are two types of PTFs, non-leaf and leaf. A non-leaf PTF is a function that accepts an input row set, optionally transforms it, and then returns an output row set. A leaf PTF does not accept an input row set. It generates an output row set from scratch or from a foreign data source.
Oracle 18.1 only supports non-leaf PTFs. Going forwards whenever this tutorial mentions "PTF" the reader should understand the reference to mean "non-leaf PTF". This advice applies to Oracle documentation as well.
Manipulating Columns
The output row set of a PTF can:
- include or exclude existing columns from the input row set
- include new columns that do not exist in the input.
Output row set columns are determined during an initial DESCRIBE phase, before rows are actually fetched from the input rowset. They can vary based on the shape of the input row set or based on the values of any non-null scalar arguments passed to the PTF. However they can not vary based on the data contained within the input row set.
This means, for example, that we can implement logic like this ...
If the input rowset contains a CREATED_BY column then add a LOWER_CREATED_BY column to the output.
... but not logic like this.
If the input rowset contains a row where CREATED_BY is a non-null value then add a LOWER_CREATED_BY column to the output.
Manipulating Rows
A PTF can return N copies of each input row (including 0 copies to produce an empty output row set). "N" is referred to as the "replication factor." This is the main, and only, mechanism for controlling the number of output rows a PTF generates.
In general usage a non-leaf PTF cannot add arbitrary rows to the output or omit arbitrary input rows from the output. It can only pass N copies of the input rows through to the output.