Techblogs

SQL Macros

Terug naar overzicht

7 februari 2024

Oracle Database 21c introduced the concept of SQL Macros. A SQL Macro is a PL/SQL function that returns a SQL snippet that is inserted into your SQL statement at the beginning of the execution cycle, so even before the statement is parsed.

Normally, when you are calling PL/SQL from SQL you are hit with context-switches that take up a lot of time (relatively). When using a SQL Macro, you don’t have this issue anymore. Yes, PL/SQL is executed, but only once at the beginning of the statement, while calling a PL/SQL function in your SQL statement executes the PL/SQL code for every row visited (if called in the where clause) or every row returned (if called in the select clause).

A SQL Macro looks a lot like a normal PL/SQL function but with the addition that it is in fact a SQL Macro. There are two types of SQL Macros. These are table-type Macros and scalar-type Macros. The table type version of the SQL Macros has been back ported to Oracle Database 19c (19.7 and up). The scalar version appears to be available in some versions of Oracle Database 19c, mostly on the autonomous database, but it is not documented and therefor not supported.

But, this blog is about Oracle Database 23c functionality, and both flavours are available there.

Syntax
A SQL Macro looks a lot like a regular function, but with the addition that it is in fact a SQL Macro.

create or replace function <macroname>( [parameters] )
return varchar2 sql_macro( table | scalar )

The function consistently returns a VARCHAR2 value, which is then substituted into the original SQL text.

Table Macros
The first category of Macros is the table-type, applicable in the FROM clause of your SQL statement. The resultant SQL snippet should, therefore, yield a dataset.

create or replace function driverconstructor
return varchar2 sql_macro( table )
is
begin
return q'[
select drv.driverid as driverid
, drv.driver_number as drivernumber
, drv.code as drivercode
, drv.driver_name as drivername
, drv.dob as driverdob
, drv.nationality as drivernationality
, con.constructorid as constructorid
, con.name as constructorname
, con.nationality as constructornationality
from drivers drv
join constructordrivers condrv on ( drv.driverid = condrv.driverid )
join constructors con on ( condrv.constructorid = con.constructorid )
]';
end;
/

You can then query the data using the SQL Macro in the FROM clause of your SQL statement:

select *
from driverconstructor()
/

The same result can be achieved by creating a conventional ( ‘normal’) view, but a SQL Macro becomes advantageous when you want to add parameters to the view. For instance, we want to see drivers and constructors where they have a certain nationality:

create or replace function driverconstructor( nationality_in in varchar2 )
return varchar2 sql_macro( table )
is
begin
return q'[
select drv.driverid as driverid
, drv.driver_number as drivernumber
, drv.code as drivercode
, drv.driver_name as drivername
, drv.dob as driverdob
, drv.nationality as drivernationality
, con.constructorid as constructorid
, con.name as constructorname
, con.nationality as constructornationality
from drivers drv
join constructordrivers condrv on ( drv.driverid = condrv.driverid )
join constructors con on ( condrv.constructorid = con.constructorid )
where ( drv.nationality like driverconstructor.nationality_in
or con.nationality like driverconstructor.nationality_in
)
]';
end;
/

When querying, you can now add nationality as a parameter, effectively creating a parameterized view:

select drivername
, drivernationality
, constructorname
, constructornationality
from driverconstructor( nationality_in => 'Dutch' )
/
DRIVERNAME DRIVERNATIONALITY CONSTRUCTORNAME CONSTRUCTORNATIONALITY
--------------- ----------------- --------------- ----------------------
Max Verstappen Dutch Red Bull Austrian
Nyck de Vries Dutch AlphaTauri Italian

select drivername
, drivernationality
, constructorname
, constructornationality
from driverconstructor( nationality_in => 'German' )
/
DRIVERNAME DRIVERNATIONALITY CONSTRUCTORNAME CONSTRUCTORNATIONALITY
--------------- ----------------- --------------- ----------------------
Lewis Hamilton British Mercedes German
Nico Hülkenberg German Haas F1 Team American
George Russell British Mercedes German

If you are concerned about SQL Injection, Oracle covers it by nullifying all parameters if used for concatenation. However, when referenced in the string, as shown above, they behave like bind variables. Numeric parameters are the exception and are not nullified.

Let's create a SQL Macro where we concatenate the input parameter:

create or replace function driverinfo( nationality_in in varchar2 )
return varchar2 sql_macro( table )
is
begin
return q'[
select drv.driverid as driverid
, drv.driver_number as drivernumber
, drv.code as drivercode
, drv.driver_name as drivername
, drv.dob as driverdob
, drv.nationality as drivernationality
from drivers drv
where drv.nationality like ']' || driverinfo.nationality_in || q'['
]';
end;
/

If we attempt to query Dutch drivers using this SQL Macro, we get an empty result set:

select *
from driverinfo( 'Dutch' )
/

no rows selected

This is because the WHERE clause looks like this:

where drv.nationality like

Changing the SQL Macro to use just the parameter, without concatenation, yields the desired result:

create or replace function driverinfo( nationality_in in varchar2 )
return varchar2 sql_macro( table )
is
begin
return q'[
select drv.driverid as driverid
, drv.driver_number as drivernumber
, drv.code as drivercode
, drv.driver_name as drivername
, drv.dob as driverdob
, drv.nationality as drivernationality
from drivers drv
where drv.nationality like driverinfo.nationality_in
]';
end;
/

Now we do get the desired result:

select *
from driverinfo( 'Dutch' )
/
DRIVERID DRIVERNUMBER DRI DRIVERNAME DRIVERDOB DRIVERNATI
-------- ------------ --- -------------------- ---------- ----------
8 33 VER Max Verstappen 30-SEP-97 Dutch
18 21 DEV Nyck de Vries 06-FEB-95 Dutch

Scalar Macros
While Table Macros are confined to the FROM clause, Scalar Macros can be employed elsewhere—in SELECT lists, WHERE clauses, GROUP BY clauses, and HAVING clauses. Scalar Macros yield a scalar value, enabling a single point of definition. This means you write the algorithm only once without incurring the context switch penalty. The prerequisite, of course, is that you must express your algorithm in pure SQL.

Suppose you have a production number used throughout your organization, and you wish to format it in a specific way. Traditionally, you might build a PL/SQL function to achieve this:

create or replace function formatnumber( productionnumber_in in varchar2 )
return varchar2
is
begin
return
regexp_replace( formatnumber.productionnumber_in, '(.{3})','\1.' )
;
end;
/

When selecting the result of this function, the context switch is triggered every time the function is called:

with t as ( select 'V000000003' value )
select value
, formatnumber( value ) formatted
from t
/

VALUE FORMATTED
--------------- ---------------
V000000003 V00.000.000.3

Now, instead of creating a PL/SQL function, you can create a Scalar SQL Macro to enforce formatting rules:

create or replace function formatnumber( productionnumber_in in varchar2 )
return varchar2 sql_macro( scalar )
is
begin
return q'[
regexp_replace( formatnumber.productionnumber_in, '(.{3})','\1.' )
]';
end;
/

The code of the macro is almost identical to the code in the function. You can even use the same SQL statement and get the same result, only faster.

While the difference may not be noticeable in a single call, it becomes significant when calling the algorithm multiple times.

Another use case for Scalar Macros might be during the migration of an application to Oracle Database. If the original database has a function now returning the system date-time, a Scalar SQL Macro can be created to perform the same function, eliminating the need to modify every query in the application:

create or replace function now
return varchar2 sql_macro( scalar ) is
begin
return 'sysdate';
end;
/
select now, sysdate
/

NOW SYSDATE
--------- ---------
29-DEC-23 29-DEC-23

Expand_sql_text
To inspect the actual SQL text executed by Oracle Database, you can use `dbms_utility.expand_sql_text`. This proves useful not only when building SQL Macros but also for investigating the performance of a query. For instance:

declare
l_input clob;
l_output clob;
begin
l_input := q'[ select sysdate ]';
dbms_utility.expand_sql_text( input_sql_text => l_input
, output_sql_text => l_output
);
dbms_output.put_line( l_output );
end;
/
SELECT SYSDATE "SYSDATE" FROM "SYS"."DUAL" "A1"

PL/SQL procedure successfully completed.

The outcome of this anonymous block might be overwhelming, so ensure you have a code formatter available or take the time to format the code yourself.

The source code in this article can be found at:
https://github.com/Qualogy-Solutions/OracleDatabase23c/blob/main/sqlmacros.sql

Also read my previous blogs about Oracle Database 23: