Scalar SQL Macros – cherry pick the best features – Learning is not a spectator sport

Scalar SQL Macros – cherry pick the best features

February 26, 2024

3 minutes

On my last Office Hours session, I handled a question from someone comparing Oracle to SQL Server, and they pointed out (paraphrasing)

“SQL Server has a ton of date related functions to make dealing with dates or date components easy. How come Oracle doesn’t do that?”

That’s probably a fair point, because a quick look through the SQL Server docs shows a plethora of functions related to date and date composition, with some of these presented below

Pretty much all of these can be performed with standard Oracle functionality using TO_NUMBER, TO_CHAR, TO_DATE functions and the like, but sometimes it might feel like doing so is unwieldy.

We could of course write our own PL/SQL functions to do mimic the SQL Server functions, but then we are just one step away from a phone call from an irate DBA once our functions go live. We are going to get berated for SQL to PL/SQL context switch overheads (smile)

This is where scalar SQL macros are such an amazing facility for when you get to 23ai (or use them already on 21c if you are on the innovation release). We now have the ability to effectively extend the functionality of the database with our own custom routines. For example, I took each the functions in the diagram above, and it was remarkably simple to build Oracle versions with SQL macros.


SQL> create or replace
  2  function day(d date) return varchar2 sql_macro(scalar) is
  3  begin
  4     return q'{to_number(to_char(d,'DD'))}';
  5  end;
  6  /

Function created.

SQL>
SQL> create or replace
  2  function month(d date) return varchar2 sql_macro(scalar) is
  3  begin
  4     return q'{to_number(to_char(d,'MM'))}';
  5  end;
  6  /

Function created.

SQL>
SQL> create or replace
  2  function year(d date) return varchar2 sql_macro(scalar) is
  3  begin
  4     return q'{to_number(to_char(d,'YYYY'))}';
  5  end;
  6  /

Function created.

SQL>
SQL>
SQL> select day(sysdate) dy from dual;

        DY
----------
        26

SQL> select month(sysdate) mm from dual;

        MM
----------
         2

SQL> select year(sysdate) yy from dual;

        YY
----------
      2024

SQL>
SQL> create or replace
  2  function datepart(part varchar2,d date) return varchar2 sql_macro(scalar) is
  3  begin
  4     return q'{
  5    case
  6      when rtrim(upper(part),'S') = 'YEAR' then to_number(to_char(d,'YYYY'))
  7      when rtrim(upper(part),'S') = 'MONTH' then to_number(to_char(d,'MM'))
  8      when rtrim(upper(part),'S') = 'DAY' then to_number(to_char(d,'DD'))
  9      when rtrim(upper(part),'S') = 'HOUR' then to_number(to_char(d,'HH24'))
 10      when rtrim(upper(part),'S') = 'MINUTE' then to_number(to_char(d,'MI'))
 11      when rtrim(upper(part),'S') = 'SECOND' then to_number(to_char(d,'SS'))
 12    end
 13         }';
 14  end;
 15  /

Function created.

SQL>
SQL> select datepart('DAY',sysdate) dp from dual;

        DP
----------
        26

SQL>
SQL>
SQL> create or replace
  2  function datefromparts(y int, m int, d int) return varchar2 sql_macro(scalar) is
  3  begin
  4     return q'{
  5       to_date(
  6         to_char(y,'fm0000')||to_char(m,'fm00')||to_char(d,'fm00'),
  7         'yyyymmdd'
  8         )
  9         }';
 10  end;
 11  /

Function created.

SQL>
SQL> select datefromparts(2023,1,17) dp from dual;

DP
---------
17-JAN-23

SQL>
SQL> create or replace
  2  function datediff(part varchar2,d2 date, d1 date) return varchar2 sql_macro(scalar) is
  3  begin
  4     return q'{
  5    case
  6      when rtrim(upper(part),'S') = 'YEAR' then year(d1)-year(d2)
  7      when rtrim(upper(part),'S') = 'MONTH' then months_between(d1,d2)
  8      when rtrim(upper(part),'S') = 'DAY' then trunc(d1-d2)
  9      when rtrim(upper(part),'S') = 'HOUR' then trunc(d1-d2)*24
 10      when rtrim(upper(part),'S') = 'MINUTE' then trunc(d1-d2)*1440
 11      when rtrim(upper(part),'S') = 'SECOND' then (d1-d2)*86400
 12    end
 13         }';
 14  end;
 15  /

Function created.

SQL>
SQL> select datediff('year',sysdate,sysdate+500) dd from dual;

        DD
----------
         1

SQL> select datediff('months',sysdate,sysdate+500) dd from dual;

        DD
----------
 16.483871

SQL> select datediff('days',sysdate,sysdate+500) dd from dual;

        DD
----------
       500

SQL> select datediff('hour',sysdate,sysdate+500) dd from dual;

        DD
----------
     12000

SQL> select datediff('minutes',sysdate,sysdate+500) dd from dual;

        DD
----------
    720000

SQL> select datediff('second',sysdate,sysdate+500) dd from dual;

        DD
----------
  43200000

SQL>
SQL> create or replace function elapsed(
  2        ts1 in timestamp,
  3        ts2 in timestamp
  4      ) return varchar2 sql_macro(scalar) is
  5  begin
  6    return '
  7      extract(day from (ts2-ts1))*86400+
  8      extract(hour from (ts2-ts1))*3600+
  9      extract(minute from (ts2-ts1))*60+
 10     extract(second from (ts2-ts1))';
 11  end;
 12  /

Function created.

SQL>
SQL> select
  2    elapsed(
  3      systimestamp,
  4      systimestamp+numtodsinterval(123,'MINUTE')) ela
  5  from dual;

       ELA
----------
      7380

SQL>

You might be migrating from SQL Server to Oracle and want to limit the amount of refactoring work, or perhaps you just see a function in SQL Server or DB2 or Postgres or MySQL that appeals to you but cannot find it in native Oracle. SQL Macros go a long way to giving you the ability to cherry pick all the goodies you like and have them all in your Oracle database.

5 responses to “Scalar SQL Macros – cherry pick the best features”

Bob Bryla

February 26, 2024 at 11:33 am

For those hapless developers and analysts that don’t even have CREATE FUNCTION privileges in DEV and have to go through an involved process to get macros like this implemented in PRD (eventually), a great way to test this and prove the value is to use SQL macro functions with a WITH clause:

with
function datepart(part varchar2, d date)
return varchar2 sql_macro(scalar) is
begin
return q'{ case
when rtrim(upper(part),’S’) = ‘YEAR’ then to_number(to_char(d,’YYYY’))
when rtrim(upper(part),’S’) = ‘MONTH’ then to_number(to_char(d,’MM’))
when rtrim(upper(part),’S’) = ‘DAY’ then to_number(to_char(d,’DD’))
when rtrim(upper(part),’S’) = ‘HOUR’ then to_number(to_char(d,’HH24′))
when rtrim(upper(part),’S’) = ‘MINUTE’ then to_number(to_char(d,’MI’))
when rtrim(upper(part),’S’) = ‘SECOND’ then to_number(to_char(d,’SS’))
end }’;
end;
function datefromparts(y int, m int, d int)
return varchar2 sql_macro(scalar) is
begin
return q'{to_date(to_char(y,’fm0000′)||to_char(m,’fm00′)||to_char(d,’fm00′),’yyyymmdd’)}’;
end;
select datepart(‘DAY’,sysdate) dp, datefromparts(2024,2,25) from dual;

DP DATEFROMPARTS(2024,
———- ——————-
25 2024-02-25 00:00:00

Reply
Sam_P

February 26, 2024 at 9:40 pm

Why doesn’t Oracle simplify Developers’ and Analysts’ lives by making these available as System Functions right out-of-the-box, like others do? Why should hundreds (and thousands) of developers need to do this manually?

Reply
1. Connor McDonald
  
  February 26, 2024 at 9:50 pm
  
  For every person that wants such a function, there will be someone who says “Hey, I’ve already used that name for my own needs”.
  
  If we were building Oracle anew, then sure, its on the cards. But when you have 40+ years of customers and databases, we have to be very mindful of what choose to add to the product.
  
  Simple example: We planned to add a role called “DEVELOPER” to 23c, but the push back was huge, because it was a user/role name already heavily used by the community. We rebadged to DB_DEVELOPER_ROLE
  
  Reply
raajeshwaran

February 26, 2024 at 10:50 pm

any specific reason to have “rtrim” in this expression?

“..when rtrim(upper(part),’S’) = ‘YEAR’…”

Reply
Connor McDonald

February 27, 2024 at 6:46 am

So people can do YEAR or YEARS etc

Reply