{"id":1510,"date":"2020-10-03T23:50:49","date_gmt":"2020-10-03T21:50:49","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=1510"},"modified":"2020-10-27T14:48:04","modified_gmt":"2020-10-27T12:48:04","slug":"sql-macros-in-oracle-19c","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/sql-macros-in-oracle-19c\/","title":{"rendered":"SQL macros in Oracle 19c"},"content":{"rendered":"\n

Now that I have created a patched Oracle database docker image<\/a> 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:<\/strong> As of now you can play with SQL macros on Autonomous Databases in Oracle Cloud (also free tier!) and on livesql.oracle.com<\/a>) <\/p>\n\n\n\n

SQL Macros<\/a> 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<\/a>. Let’s see if it works, and how.<\/p>\n\n\n\n\n\n\n\n

It is a bug 30324180<\/a>, where the backport is mentioned. And the fix is first included in 19.6. So, strictly speaking, you do not need 19.8 that I’m working with – 19.6 should also be just fine. (UPDATE:<\/strong> 19.6 is mentioned in the bug document, but I was able to run the example from this post on an ADW database in the cloud with 19.5 right now)<\/p>\n\n\n\n

Let’s start with a basic example from my previous post<\/a> – a scalar macro function JOB_DURATION:<\/p>\n\n\n

\nSQL> CREATE OR REPLACE FUNCTION job_duration RETURN VARCHAR2 \n  2  SQL_MACRO(SCALAR) \n  3  AS\n  4  BEGIN\n  5     RETURN q'! FLOOR(MONTHS_BETWEEN (SYSDATE, hiredate)\/12) !';\n  6  END;\n  7  \/\n\nFunction JOB_DURATION compiled\n\nLINE\/COL  ERROR\n--------- -----------------------------------------------\n2\/10      PLS-00103: Encountered the symbol "(" \nwhen expecting one of the following:     ...\n<\/pre><\/div>\n\n\n
Listing 1: no scalar macros in 19c<\/em><\/strong><\/p>\n\n\n\n
Couldn’t compile… Why? The error message complains about the parenthesis around SCALAR<\/code><\/strong>, but not about the word SQL_MACRO<\/code><\/strong>. There is a good hint in the  description of the “backport” bug 30324180 to understand this. Scalar macros were intentionally not backported<\/strong>! So you only have table semantic macros (which are also the default in 20c) and it seems that you cannot explicitly specify the type. The good news here is that you don’t have to change anything when you migrate to 20c. So let’s focus on the table semantic functions then. <\/p>\n\n\n\n
Get your data CSV formatted<\/h2>\n\n\n\n
As an example, I wanted to take something that would be useful in itself and at the same time can show the other capabilities of what can be achieved with table SQL macros. Since I work a lot as an ETL engineer, I am often faced with the task of exporting data as CSV. Not my favorite format, to be honest, especially because you lose a lot of metadata. But there are often project requirements you have no control over. And if you have to<\/strong> export data as CSV, it is very important that it is done consistently in the same way in larger projects. And that’s where SQL macros can be very helpful! Let’s see why and how. <\/p>\n\n\n\n
Our goal is to develop a generic<\/strong> function:<\/p>\n\n\n\n