In the previous post I showed how to get to the final query after SQL macro expansion using CBO trace. But there are still a few interesting things in the trace file that are worth looking at.
Continue reading
A few days ago I received a comment/question on the older post about dynamically transposing rows to column with Polymorphic Table Functions (PTF). Back then I overlooked a bug in the example code, but the explanation takes a bit longer, so i decided to write a new post about it. The PTF was working initially but after inserting new data started to return wrong results – all NULL’s for some columns where we know there are actually values present. So, what’s going on?
Continue reading
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…
This note is meant more as an illustrative example for a question, because the behavior seems strange to me. If I want to do something in my SQL macro that needs additional privileges, how it will run then, when other users will call my SQL macro?
Continue reading
I had to learn from this mistake more than once in my SQL career, until it became a kind of reflex: always think about the NULL values when you are writing a NOT IN subquery. Well, you should ALWAYS keep the NULL values in mind as a SQL developer, but in this case the consequences can be really nasty! And it drives me crazy how many times I’ve seen this error in my consulting life, even in production code. So, what is the point here?
Continue reading
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
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
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
Yesterday I voted on the poll by @SQL Daily about using different forms for “not equals” conditions and that brought back bad (and funny) memories. Over 25 years I have always used “!=”. “Does it matter”, you might ask? Actually, it shouldn’t. But here is my story and the reason, why I was considering to change my habits and use “<>”.
Continue reading
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.