Tag Archives: 20c

Dynamic Pivot with SQL Macros in Oracle 20c

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

Temporal Joins with SQL Macros in Oracle 20c

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.

Continue reading