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
Not Every “Not Equals” is Equal
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
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.
SQL macros in Oracle 19c
Now that I have created a patched Oracle database docker image with Release Update 19.8 (18.104.22.168.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
Upgrading a 19c database on docker
I’m neither an Oracle DBA, nor am I a Docker expert. How on earth did I get here to write this post? Well, I’m still an Oracle Developer and as you may have noticed, I was recently focused on SQL macros. It is a new feature of Oracle Database 20c, but obviously so awesome that it was backported to 19.6.0. Of course I asked myself, how can I start testing SQL macros in 19c?Continue reading
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
Parameterized Views in Oracle? No problem! With SQL macros!
Views have always been an efficient tool for encapsulating complex logic, creating defined access structures and so on. But there is one thing views cannot do: accept parameters. And this is unfortunately a big disadvantage in terms of flexibility. As a result, there are a number of workarounds, none of them without their drawbacks.Continue reading