SQL Macros

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.

Syntax

A SQL Macro looks a lot like a regular function, but with the addition that it is in fact a SQL Macro. The function consistently returns a VARCHAR2 value, which is then substituted into the original SQL text.

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

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. 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.

Basic Table Macro Example:

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;

Parameterized Table Macro:

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;

SQL Injection Protection:

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.

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.

Scalar Macro Example:

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;

Migration Use Case:

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

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.

Example:

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;

The source code in this article can be found at:

https://github.com/Qualogy-Solutions/OracleDatabase23c/blob/main/sqlmacros.sql

Syntax

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

Table Macros

Basic Table Macro Example:

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;

Parameterized Table Macro:

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;

SQL Injection Protection:

Scalar Macros

Scalar Macro Example:

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;

Migration Use Case:

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

Expand_sql_text

Example:

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;

The source code in this article can be found at:

https://github.com/Qualogy-Solutions/OracleDatabase23c/blob/main/sqlmacros.sql

Syntax

Table Macros

Basic Table Macro Example:

Parameterized Table Macro:

SQL Injection Protection:

Scalar Macros

Scalar Macro Example:

Migration Use Case:

Expand_sql_text

Example:

The source code in this article can be found at:

Also read my previous blogs about Oracle Database 23:

SQL Macros

Syntax

Table Macros

Basic Table Macro Example:

Parameterized Table Macro:

SQL Injection Protection:

Scalar Macros

Scalar Macro Example:

Migration Use Case:

Expand_sql_text

Example:

The source code in this article can be found at:

Also read my previous blogs about Oracle Database 23: