More than a year ago I blogged about my view on using Polymorphic Table Function (PTF) for dynamic pivot in Oracle 18c. Actually I was not as optimistic. The solution had at least two significant problems:
- the function will not see any changes on the underlying data until new hard parse of the query
- operating on bigger data sets returns multiple result rows: one per 1024 rows (the size of the row sets in PTF), thus requiring some post processing
With Oracle 20c we now have SQL macros and I was curious, whether they can help here.
Continue reading