Tag Archives: SQL macros

SQL macros and the WITH clause

Leave a reply

SQL macros and WITH clause are not known to be great friends: you cannot call a SQL macro in a WITH clause and if you want to define a table macro returning a query containing a WITH subquery(ies), then you won’t be able to use scalar parameters in this subquery. In the previous post I tried to explain another restriction from the technical perspective looking at the SQL macro expansion trace information. In this post I will try to understand the WITH clause related limitations using the same approach.

Continue reading →

SQL Macros – Changing The Query Shape Using Parameters?

Leave a reply

One thing I learned early on when I was getting to know SQL macros is that we cannot replace each and every part of a SQL statement with a macro. In the same way, working with table SQL macros, we cannot represent just any part of the result string by referencing parameters inside it. Sometimes we need to use string concatenation. But why it behaves this way? In this post I will look at the available trace information about SQL macro expansion during hard parse and try to understand its impact on this restriction. After that we’ll also look how to overcome it and what to consider.

Continue reading →

Parameterized Views with SQL Macros (Part 2)

6 Replies

One of the most mentioned use cases for table SQL macros is the implementation, which works like a parameterized view. My first post on this is from 2020 and it ends with a thought that we have many advantages here but no real view at all. And indeed, I have heard this argument several times since then. Views are schema objects that we have been familiar with for decades, they are very easy to develop and deploy, and their query code is easily accessible via data dictionary. On the other hand, maintaining query code within SQL macros can be a bit more difficult and understandably not to everyone’s preference. But what if you could have a cake and eat it too? Here is how…

Continue reading →

SQL Macros – Some Less Obvious Facts (Part 2)

Leave a reply

This second part of the “less obvious facts” series is about the COLUMNS pseudo-operator. It has been introduced in 18c as one of the variadic pseudo-operators, intended to operate with a variable number of operands. Unfortunately, no other pseudo-operators have been introduced since then, so that it is worth looking at what exactly the COLUMNS operator is, how to use it and how it can potentially be “misused”.

Continue reading →

SQL Macros – Some Less Obvious Facts (Part 1)

Leave a reply

Since I have written a lot about SQL macros, I have tested some less obvious, sometimes surprising cases. But expanding about them in the original posts would just overload them, so I decided to make a dedicated post for that. The other good reason for this is that the SQL macros are, in my opinion, not documented in enough detail. So additional research and testing can be useful.

Continue reading →

How to get the final SQL after macro expansion

3 Replies

In my very first post about SQL macros, I mentioned that for table macros, there is a simple way to see the SQL statement after macro expansion using dbms_utility.expand_sql_text. However, for scalar SQL macros, there is no such straightforward method. We can activate a CBO trace (also known as event 10053) and find the final statement in the trace file. This approach works for both scalar and table SQL macros. In this post, we will explore how to do this, and we will use… a SQL macro for that! Well, at least we will give it a try…

Continue reading →

What does ChatGPT “think” about SQL Macros?

Leave a reply

After some weeks of reading and hearing in almost every news report about generative AI and ChatGPT in particular, and how it will change a developer’s life or even make developers obsolete, I was really curious to check it out myself and ask some developer questions. Since I’ve posted quite lot about SQL macros in the past few years, I thought aksing about them could be a good start point. One more argument to start with the topic I’m pretending to know lot about, is to be able to easily distinguish the truth from AI hallucination.

Continue reading →

Comparing Rows using a SQL Macro

Leave a reply

After the previous post about building an UNPIVOT query via SQL macro to output table rows like key-value pairs, I thought of another use case for the UNPIVOT operator, where developing a reusable SQL macro will make sense. From time to time you need to spot the difference between almost identical rows. You know they are different and you can easily check this using MINUS or GROUP BY, but if you want to know in what column(s) exactly the difference is, you need another approach.

Continue reading →