How to Write Scalar SQL Macros in Oracle

Calling a PL/SQL function from SQL switches from the SQL engine to PL/SQL for every row. A scalar SQL macro avoids that: it is a function that returns the text of a SQL expression, and Oracle puts that text into the query when it is parsed. You write the logic once, reuse it in many queries, and the query runs as plain SQL.

Code for This Guide

The main examples are in the examples/advanced-plsql folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create [or replace] function name (parameters)
  return varchar2 sql_macro(scalar)
is
begin
  return 'sql expression that uses the parameter names';
end;

Inside the returned text, parameter names stand for the arguments of the call, so the macro is expanded with the real columns or values the query passes.

A Reusable Expression

MINUTES_BETWEEN returns an expression that turns the interval between two timestamps into minutes. The query uses it for the block time of three flights.

Example:

create or replace function minutes_between (p_from timestamp with time zone,
                                            p_to   timestamp with time zone)
  return varchar2 sql_macro(scalar)
is
begin
  return 'extract(day from (p_to - p_from)) * 1440
          + extract(hour from (p_to - p_from)) * 60
          + extract(minute from (p_to - p_from))';
end;
/
select flight_no, minutes_between(scheduled_departure, scheduled_arrival) as block
from   flights
where  flight_id <= 3;

Output:

Function MINUTES_BETWEEN compiled

FLIGHT_NO       BLOCK
____________ ________
NM128             423
NM126             230
NM113             818

At parse time, minutes_between(scheduled_departure, scheduled_arrival) is replaced by the EXTRACT expression with those two columns, so no PL/SQL runs while the rows are read.

Use It in WHERE

A scalar macro can go anywhere an expression can, such as a WHERE clause.

Example:

create or replace function minutes_between (p_from timestamp with time zone,
                                            p_to   timestamp with time zone)
  return varchar2 sql_macro(scalar)
is
begin
  return 'extract(day from (p_to - p_from)) * 1440
          + extract(hour from (p_to - p_from)) * 60
          + extract(minute from (p_to - p_from))';
end;
/
-- the macro works anywhere an expression does, including WHERE
select count(*) as long_flights
from   flights
where  minutes_between(scheduled_departure, scheduled_arrival) > 600;

Output:

Function MINUTES_BETWEEN compiled

   LONG_FLIGHTS
_______________
            720

The query counts the flights scheduled for more than ten hours. The example drops the function afterward.

Things to Know

• The function runs once, when the query is parsed, not once per row; it only builds text.
• The returned text must be a valid SQL expression; errors in it appear when a query using the macro is parsed.
• A macro called directly from PL/SQL, outside a SQL statement, is just a function that returns a string.

Related Guides

• How to Speed Up SQL Calls with PRAGMA UDF
• How to Use DETERMINISTIC Functions in PL/SQL

Conclusion

A scalar SQL macro is a function marked SQL_MACRO(SCALAR) that returns the text of an expression. Oracle puts the expression into the query at parse time, so you get reusable logic without switching to PL/SQL for every row.