Tag Archives: SQL macros

SQL Macros – Some Less Obvious Facts (Part 2)

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)

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

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?

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

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

Yet another PRINT_TABLE – as a SQL Macro!

A few weeks ago Jonathan Lewis has published a note about Tom Kyte’s print_table – a small PL/SQL procedure to output each row in a table as a list of (column_name , value). And since this note has gained some comments with other implementations, here is my contribution. Guess how? Of course with a SQL macro.

Continue reading

Using Subqueries and Views with PTF or SQL macros

In the last post about SQL macros in 19c I tried to use a subquery as an input for the function. And when I had some DATE calculations in a query, I got ORA-62558: Unsupported data types (DATE) The result looked somewhat confusing at first, but if you think about it, it is logical and maybe not a bug at all. You should just be very explicit while using subqueries and views(!) in this scenario. And because this behavior is not documented, it is worth sharing, I think.

Continue reading

SQL macros in Oracle 19c

Now that I have created a patched Oracle database docker image with Release Update 19.8 (19.8.0.0.DBRU:200714) to play with, I can finally start testing the backport of SQL macros! (UPDATE: As of now you can play with SQL macros on Autonomous Databases in Oracle Cloud (also free tier!) and on livesql.oracle.com)

SQL Macros have been introduced in Oracle 20c. As of now, Oracle 20c is only available as preview version in Oracle Cloud. That’s why I was quite excited when Connor McDonald pointed out on Twitter that they were backported to 19c. Let’s see if it works, and how.

Continue reading