create or replace function row_generator(
p_rows in number
) return varchar2 sql_macro(table)
is
begin
return '
select level as id
from dual
connect by level <= p_rows
';
end row_generator;
/
with base as (
select id from row_generator(4)
)
select id
from base
/
--ORA-64630: unsupported use of SQL macro: use of SQL macro inside WITH clause is not supported
drop function row_generator;- Getting Started
- SQL Macros Now Fully Supported in 19c r25
- Row Generator Table Macros
- Nested Table Macros: An Exponent Generator
- Complex Joins In Table Macros: Factorial Generator Macro
- Macros and Nested Inline Views: A Calendar Macro
- Passing Data Sources To Macros: The Split String Macro
- Encapsulate Complex Logic: The Days Until Macro
- Complex Display Expressions: The Display Intervals Macro
- Regular Expressions: Format UUID Macro
- Scalar Macros Calling Table Macros: Factorial Function Macro
- Table Macros Using Scalar Macros: Another Factorial Generator
- Subqueries in Scalar Macros: Analysing Vector Dimensions
SQL macro functions introduced in Oracle 21c offer a new option to consolidate complex SQL logic without the cost of calling PL/SQL functions from an SQL statement. At this time, the use of a SQL macro inside a WITH clause is still not supported.
This does not mean that the definition of a SQL macro inside a WITH query is unsupported. The WITH clause supports local functions, and sql macro functions can be defined as local functions in a WITH statement.
Defining SQL macros in a WITH statement allow you to run ad hoc macros in environments where you can’t compile functions. This approach is also a great way to quickly prototype sql macros and get to learn their capabilities and limitations.
Since there is nothing to compile, all of the examples in this article are self contained and can be easily run as is. After working with a few basic examples, you will be ready to unleash your imagination and explore the possibilities that sql macros open up.
Getting Started
The source code for all articles here on practicalplsql.org is available in the practicalplsql public repository on github in the scripts/articles directory organized by concept and article. You can use create.user.practicalplsql.sql to create the practicalplsql schema. Sample data for articles can be found in the scripts/examples directory organized by example name.
Scripts for this post are located in sql-macros/with-function-macros.
SQL Macros Now Fully Supported in 19c r25
Table macros were backported to Oracle 19c in release 7. As of 19c release 25, scalar macros are now fully supported. Even if you are still waiting to upgrade to Oracle 23ai, there is no longer a reason to wait to explore the full capability of SQL macros. All of the macros in this article (except for the vector macro) will run in Oracle 19r25 or Oracle 23ai.
Row Generator Table Macros
Table macros work as a rowsource in the FROM clause of a query, the text that the macro returns is substituted for the macro call on parsing.
A row generator macro is a basic necessity for generating test data, and it is worth exploring a few variations on the theme before deciding on a final version.
--table-macro-01-row-generator.sql
with
function row_generator(
p_rows in number
) return varchar2 sql_macro(table)
is
begin
return q'[
select level as n
from dual
connect by level <= p_rows
]';
end row_generator;
select b.n
from row_generator(4) b
/
N
-
1
2
3
4We can use a dbms_tf.columns_t collection to pass in a column alias for the row generator:
--table-macro-02-row-generator-aliased.sql
with
function row_generator(
p_rows in number
, p_alias in dbms_tf.columns_t
) return varchar2 sql_macro(table)
is
l_sql varchar2(1000);
begin
l_sql := q'[
select level as ##alias##
from dual
connect by level <= p_rows
]';
l_sql := replace(l_sql, '##alias##', p_alias(1));
return l_sql;
end row_generator;
select x
from row_generator(4, columns(x))
/
X
-
1
2
3
4Nested Table Macros: An Exponent Generator
A macro can be defined that uses other macros. The invocations of the macros cannot be nested directly, but each macro can call other macros. In this example, the exponent_generator uses the row_generator to make a chart of exponent values for a given base.
--table-macro-03-exponent-generator.sql
with
function row_generator(
p_rows in number
, p_alias in dbms_tf.columns_t
) return varchar2 sql_macro(table)
is
l_sql varchar2(1000);
begin
l_sql := q'[
select level as ##alias##
from dual
connect by level <= p_rows
]';
l_sql := replace(l_sql, '##alias##', p_alias(1));
return l_sql;
end row_generator;
function exponent_generator(
p_base in number
, p_max_power in number
) return varchar2 sql_macro(table)
is
begin
return q'~
select
p_base as "base"
, n - 1 as "exponent"
, power(p_base, n - 1) as "result"
, p_base || '^' || (n - 1)
|| ' = ' || power(p_base, n - 1) as "equation"
from row_generator(p_max_power + 1, columns(n))
~';
end exponent_generator;
select *
from exponent_generator(2, 8)
/
base exponent result equation
---- ---------- ------ ------------
2 0 1 2^0 = 1
2 1 2 2^1 = 2
2 2 4 2^2 = 4
2 3 8 2^3 = 8
2 4 16 2^4 = 16
2 5 32 2^5 = 32
2 6 64 2^6 = 64
2 7 128 2^7 = 128
2 8 256 2^8 = 256 Complex Joins In Table Macros: Factorial Generator Macro
Table macros can also encapsulate complex join logic. In this example we build a chart showing the definitions of factorials. The cross apply join between the two calls to the row generator allow the rows of the first call to be used to define rows for the second call:
--table-macro-04-factorial-generator.sql
with
function row_generator(
p_rows in number
, p_alias in dbms_tf.columns_t
) return varchar2 sql_macro(table)
is
l_sql varchar2(1000);
begin
l_sql := q'[
select level as ##alias##
from dual
connect by level <= p_rows
]';
l_sql := replace(l_sql, '##alias##', p_alias(1));
return l_sql;
end row_generator;
function factorial_generator(
p_max_n in number
) return varchar2 sql_macro(table)
is
begin
return q'~
select
a.n as "n"
, round(exp(sum(ln(b.m)))) as "n!"
, a.n || '! = '
|| listagg(b.m, ' x ') within group (order by b.m desc)
|| ' = ' || round(exp(sum(ln(b.m)))) as "expression"
from
(
select n
from row_generator(p_max_n, columns(n))
) a
cross apply
(
select m
from row_generator(a.n, columns(m))
) b
group by a.n
~';
end factorial_generator;
select *
from factorial_generator(4)
/
n n! expression
--- ---------- ------------------------------
1 1 1! = 1 = 1
2 2 2! = 2 x 1 = 2
3 6 3! = 3 x 2 x 1 = 6
4 24 4! = 4 x 3 x 2 x 1 = 24 Macros and Nested Inline Views: A Calendar Macro
Since its New Year’s Eve, we can make a simple calendar. The query returned by the macro can have multiple levels of nested inline views. Notice that the p_start_year parameter is used by the innermost inline view.
--table-macro-05-calendar-generator.sql
alter session set nls_date_format='fmMonth dd, yyyy';
column calendar_date format a20
with
function calendar_generator(
p_start_year in number default null
, p_years in number default 1
) return varchar2
sql_macro(table)
is
begin
return q'[
select
a.start_date + b.n as calendar_date
from
(
select
s.start_date
, s.start_date
+ numtoyminterval(p_years, 'year')
- interval '1' day
as end_date
from
(
select
trunc(
to_date(
nvl(p_start_year, extract(year from sysdate))
, 'yyyy')
, 'yyyy')
as start_date
from dual
) s
) a
cross apply
(
select level - 1 as n
from dual
connect by level <= (a.end_date - a.start_date + 1)
) b
order by calendar_date
]';
end calendar_generator;
select *
from calendar_generator(2025)
/
CALENDAR_DATE
--------------------
January 1, 2025
January 2, 2025
January 3, 2025
January 4, 2025
...(rows elided)...
December 29, 2025
December 30, 2025
December 31, 2025
365 rows selected. Passing Data Sources To Macros: The Split String Macro
We can also pass other CTE expressions, tables or views to a table macro. The split_string function made famous on askTom is an excellent candidate for a table macro.
In this example, rows with columns containing delimited strings can easily be parsed into separate rows. The data source is passed using a dbms_tf.table_t type, the column containing the delimited values and the desired alias for the parsed values are passed using a dbms_tf.columns_t type.
--table-macro-06-split-strings.sql
with
function split_strings(
p_data in dbms_tf.table_t
, p_delim_column in dbms_tf.columns_t
, p_value_alias in dbms_tf.columns_t
) return varchar2 sql_macro(table)
is
l_sql varchar2(4000);
begin
l_sql := q'~
select b.*, s.##split_value_alias##
from
p_data b
outer apply
(
select ##split_value_alias##
from
(
select
regexp_substr(
b.##delimited_values##
,'[^,]+'
,1
, x.pos) as ##split_value_alias##
from
(
select level as pos
from dual
connect by level <=
length(
regexp_replace(
b.##delimited_values##
, '[^,]')
) + 1
) x
)
where ##split_value_alias## is not null
) s
~';
l_sql := replace(l_sql, '##split_value_alias##', p_value_alias(1));
l_sql := replace(l_sql, '##delimited_values##', p_delim_column(1));
return l_sql;
end split_strings;
base (id, detail_list) as (
select 1, 'aa,bb,cc,' from dual union all
select 2, 'xxx,yyyy,,zzzz' from dual union all
select 3, null from dual
)
select s.*
from split_strings(base, columns(detail_list), columns(detail_item)) s
/
ID DETAIL_LIST DETAIL_ITEM
--- -------------- ------------
1 aa,bb,cc, aa
1 aa,bb,cc, bb
1 aa,bb,cc, cc
2 xxx,yyyy,,zzzz xxx
2 xxx,yyyy,,zzzz yyyy
2 xxx,yyyy,,zzzz zzzz
3 (null) (null) These are just some examples of the power of sql table macros. Adding scalar sql macros to the mix opens up the possibilities even further.
Encapsulate Complex Logic: The Days Until Macro
Scalar macros can be used to allow easy reuse of complex sql expressions without repetitive code. Columns are directly passed to scalar macros to allow their values to be substituted in the macro text. When the query executes, the actual value of the columns is used to compute the scalar expression. Calculating the number of days until an annual event uses the same complex expression as determining when the even will happen. With a scalar macro, this complex expression can be defined once and reused multiple times:
--scalar-macro-01-days-until.sql
with
function days_until(p_holiday_mm_dd in varchar2
) return varchar2 sql_macro(scalar)
is
begin
return q'[
case sign(trunc(sysdate) - to_date(p_holiday_mm_dd, 'mm-dd'))
when 1 then add_months(to_date(p_holiday_mm_dd, 'mm-dd'), 12)
else to_date(p_holiday_mm_dd,'mm-dd')
end
- trunc(sysdate)
]';
end days_until;
holidays(mm_dd, holiday_name) as (
select '12-31', 'new year''s eve' from dual union all
select '10-31', 'halloween' from dual union all
select '12-25', 'christmas' from dual union all
select '02-14', 'valentine''s' from dual
)
select
holiday_name as "holiday"
, days_until(mm_dd) as "days to wait"
, days_until(mm_dd) + trunc(sysdate) as "waiting ends"
from holidays
order by "days to wait"
/
holiday days to wait waiting ends
-------------- ------------ -----------------
new year's eve 0 December 31, 2024
valentine's 45 February 14, 2025
halloween 304 October 31, 2025
christmas 359 December 25, 2025Complex Display Expressions: The Display Intervals Macro
Scalar macros are ideal for complex presentation logic. A report with interval values can benefit from some reusable formatting in a scalar macro:
--scalar-macro-02-display-interval.sql
with
function display_interval(
p_interval in dsinterval_unconstrained
, p_seconds_precision in number default 2
)return varchar2 sql_macro(scalar)
is
begin
return q'[
trim(
case when extract(day from p_interval) > 0 then
extract(day from p_interval) || ' days '
end
|| case when extract(hour from p_interval) > 0 then
extract(hour from p_interval) || ' hr '
end
|| case when extract(minute from p_interval) > 0 then
extract(minute from p_interval) || ' min '
end
|| case when extract(second from p_interval) > 0 then
round(
extract(second from p_interval)
, p_seconds_precision)
|| ' sec'
end
)
]';
end display_interval;
base (interval_value) as (
select numtodsinterval(dbms_random.value(0, 1) * 100000, 'second')
from dual
connect by level <= 5
)
select
display_interval(interval_value) as format_interval
, display_interval(interval_value/2) as format_half_interval
, display_interval(interval_value/10) as format_tenth_interval
from base
/
FORMAT_INTERVAL FORMAT_HALF_INTERVAL FORMAT_TENTH_INTERVAL
------------------------------ ------------------------------ ------------------------------
1 hr 41 min 45.03 sec 50 min 52.51 sec 10 min 10.5 sec
8 hr 12 min 21.37 sec 4 hr 6 min 10.68 sec 49 min 14.14 sec
9 hr 22 min 36.37 sec 4 hr 41 min 18.19 sec 56 min 15.64 sec
17 hr 55 min 31.82 sec 8 hr 57 min 45.91 sec 1 hr 47 min 33.18 sec
1 days 1 hr 12 min 42.27 sec 12 hr 36 min 21.13 sec 2 hr 31 min 16.23 sec Regular Expressions: Format UUID Macro
Complex formatting or transformations performed with regular expressions are easily encapsulated in a scalar macro. This makes the final SQL statement more readable and the intention of the regular expressions is clear:
--scalar-macro-03-format-uuid.sql
with function format_uuid(
p_guid in raw
) return varchar2 sql_macro(scalar)
is
begin
return q'~
regexp_replace(
rawtohex(p_guid)
,'(.{8})(.{4})(.{4})(.{4})(.{12})'
,'\1-\2-\3-\4-\5')
~';
end format_uuid;
base as (
select sys_guid() as uuid from dual
connect by level <= 5
)
select b.uuid, format_uuid(b.uuid) as fmt_uuid
from base b
/
UUID FMT_UUID
-------------------------------- ------------------------------------
2A938F82EC342947E063FE59000A6C54 2A938F82-EC34-2947-E063-FE59000A6C54
2A938F82EC352947E063FE59000A6C54 2A938F82-EC35-2947-E063-FE59000A6C54
2A938F82EC362947E063FE59000A6C54 2A938F82-EC36-2947-E063-FE59000A6C54
2A938F82EC372947E063FE59000A6C54 2A938F82-EC37-2947-E063-FE59000A6C54
2A938F82EC382947E063FE59000A6C54 2A938F82-EC38-2947-E063-FE59000A6C54Scalar Macros Calling Table Macros: Factorial Function Macro
Because scalar macros are used to define the expression for a column, they can contain scalar subqueries and even call table macros.
--scalar-macro-04-factorial.sql
with
function row_generator(
p_rows in number
, p_alias in dbms_tf.columns_t
) return varchar2 sql_macro(table)
is
l_sql varchar2(1000);
begin
l_sql := q'~
select level as ##alias##
from dual
connect by level <= p_rows
~';
l_sql := replace(l_sql, '##alias##', p_alias(1));
return l_sql;
end row_generator;
function factorial(
p_n in integer
) return varchar2 sql_macro(scalar)
is
begin
return q'~
select
round(exp(sum(ln(n)))) as "n!"
from row_generator(p_n, columns(n))
~';
end factorial;
select
x as "x"
, factorial(x) as "x!"
from row_generator(5,columns(x))
/
x x!
- ----------
1 1
2 2
3 6
4 24
5 120Table Macros Using Scalar Macros: Another Factorial Generator
Table macros can also use scalar macros in their definitions. Here is a revision of the first factorial generator that uses a mix of scalar and table macros working together to generate a chart of factorials:
--scalar-macro-05-factorial.sql
with
function row_generator(
p_rows in number
, p_alias in dbms_tf.columns_t
) return varchar2 sql_macro(table)
is
l_sql varchar2(1000);
begin
l_sql := q'~
select level as ##alias##
from dual
connect by level <= p_rows
~';
l_sql := replace(l_sql, '##alias##', p_alias(1));
return l_sql;
end row_generator;
function factorial(
p_n in integer
) return varchar2 sql_macro(scalar)
is
begin
return q'~
select
round(exp(sum(ln(n)))) as "n!"
from row_generator(p_n, columns(n))
~';
end factorial;
function factorial_expansion(
p_n in integer
) return varchar2 sql_macro(scalar)
is
begin
return q'~
select
listagg(n, ' x ') within group (order by n desc)
from row_generator(p_n, columns(n))
~';
end factorial_expansion;
function factorial_expression(p_n in integer)
return varchar2 sql_macro(scalar)
is
begin
return q'~
p_n || '! = ' || factorial_expansion(p_n) || ' = ' || factorial(p_n)
~';
end factorial_expression;
function factorial_generator(
p_max_n in number
) return varchar2 sql_macro(table)
is
begin
return q'~
select
n as "n"
, factorial(n) as "n!"
, factorial_expression(n) as "expanded"
from row_generator(p_max_n, columns(n))
~';
end factorial_generator;
select * from factorial_generator(7)
/
n n! expanded
--- ---------- ----------------------------------------
1 1 1! = 1 = 1
2 2 2! = 2 x 1 = 2
3 6 3! = 3 x 2 x 1 = 6
4 24 4! = 4 x 3 x 2 x 1 = 24
5 120 5! = 5 x 4 x 3 x 2 x 1 = 120
6 720 6! = 6 x 5 x 4 x 3 x 2 x 1 = 720
7 5040 7! = 7 x 6 x 5 x 4 x 3 x 2 x 1 = 5040 Subqueries in Scalar Macros: Analysing Vector Dimensions
In the split strings macro, we decomposed a column into rows using a table macro. We can also decompose a complex value into rows for analysis and then return a grouped result in a scalar subquery expression. A vector is an array of values, here we use a scalar macro to decompose each of the dimension values in a vector and return an aggregate summary of the min and max range for all of the vector dimensions:
--scalar-macro-06-vector-dimension-max.sql
--Note: vectors are only supported in Oracle 23ai and higher
with
function vector_dimension_range(
p_vector in vector
) return varchar2 sql_macro(scalar)
is
begin
return q'~
select min(j.dim) || ' to ' || max(j.dim)
from json_table(
from_vector(p_vector), '$[*]'
columns (dim number path '$')
) j
~';
end vector_dimension_range;
base(vec) as (
select to_vector('[-3,4,-5,6]',*,int8) union all
select to_vector('[0.345, 0.112, -2.17]', *, float32)
)
select
b.vec
, vector_dimension_range(b.vec) as vec_dim_range
from base b
/
VEC VEC_DIM_RANGE
------------------------------------------------------------ ------------------------------
[-3,4,-5,6] -5 to 6
[3.44999999E-001,1.12000003E-001,-2.17000008E+000] -2.17000008 to .344999999 These examples are just a small sample of what sql macros can be used for to spark your imagination.
Prototyping macros in the WITH clause offers a quick and easy way to explore the potential of sql macros and begin developing a library of macros for all occasions.
Happy New Year!
–Anthony Harper
Practical Pl/Sql 
Leave a comment