Category Archives: General

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 →

Not Every “Not Equals” is Equal

Leave a reply

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 →

Using Subqueries and Views with PTF or SQL macros

1 Reply

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

8 Replies

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 →

Dynamic Pivot with SQL Macros in Oracle 20c

Leave a reply

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 →

Building Hash Keys using SQL Macros in Oracle 20c

5 Replies

In the next post about SQL macros in Oracle 20c we look at how they could be useful for building hash keys. If you are familiar with Data Warehousing and Data Vault modelling approach, you will probably know why it can be a good idea to build hash keys or hash diffs. Anyway, we will not discuss whether or not you should use them, but rather how you can do this in Oracle in a consistent and performant way.

Continue reading →

Issue with the Hint ENABLE_PARALLEL_DML

Leave a reply

Performing an ETL with large data sets, it is often a good idea to run DML in parallel. But, in contrast to parallel query or DDL, parallel DML has to be explicitly enabled. You had to issue ALTER SESSION ENABLE PARALLEL DML in the past. Starting with 12c you can enable parallel DML specifically for each query using the hint ENABLE_PARALLEL_DML. For a few years now, I’ve been using the hint now and then and was quite happy. An observation I made a few days ago can lead to a rethinking. What I could observe is that for the SQL with embedded hint a new child cursor was created each time. Let’s test it!

Continue reading →

Dynamic Pivot with Polymorphic Table Function?

Leave a reply

LiveSQL is great place to start playing with new features. It provides a couple of very helpful demo scripts explaining how polymorphic table functions work. There I found a new script few days ago which uses PTF for dynamic pivot! WOW! According to my subjective perception, it seems to be one of the most desired features in Oracle SQL! But let’s have a closer look. Is this really feasible and mature enough to be used in production code?

Continue reading →

Polymorphic Table Functions (PTF) , Part 3 – Row Replication

5 Replies

In the third part of the PTF-series we learn how a PTF can change the cardinality of the input data flow: return more or less rows as in the input. We’ll use the same simple table as in the part 2 and our new task will be column transposing. We’ll still define, which columns have to stay unchanged (as we already did using the parameter cols2stay). All other columns should be displayed as key-value pairs. Continue reading →