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.
In a previous post on SQL macros in Oracle Database 20c we saw how SQL macros can be used to create a kind of “parameterized” views to establish a simplified access tier to temporal data. In this post I’d like to explore more possibilities to hide the complexity of SQL statements behind a functional syntax provided by using SQL macros. As an example we’ll stay with a temporal data introduced in the previous post and explore how we can do a temporal join of this versioned data.
In this part we will keep focus on scalar macros going into more detail and look at parameter passing and parsing.
The preview version of Oracle 20c is now available in the cloud and I got the chance to test some new features there. I start with SQL macros, a feature that I think could well become a kind of game changer: let’s just look at the long-awaited parameterized views.