WITH Function Macros

			
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;

		

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
4

		

We 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
4

		

Nested 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, 2025

		

Complex 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-FE59000A6C54

		

Scalar 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        120

		

Table 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

Discussion and Comments

3 responses to “WITH Function Macros”

andrejpashchenko

July 6, 2025

Hi Anthony

are you sure about supporting scalar SQL macros in 19r25? Where can I read more about it? It doesn’t work for me and I haven’t found something in New Features documentation yet.Kind regardsAndrej

Loading…

1. Anthony Harper
  
  July 6, 2025
  
  Hi Andrej,
  
  I am not sure why but this has not been backported to some editions.
  And it is not mentioned at all in 19c Release updates either.
  
  Chris Saxon tested using 19.25 Extreme Edition and was unable to run scalar macros.
  
  I was using OCI Autonomous Database release 25 when I first saw this work.
  
  Currently OCI Autonomous database is on release 28 and scalar macros still work:
  
  column banner_full format a80
  select banner_full from v$version; with function add_one(n in number) return varchar2 sql_macro(scalar) is begin return 'n + 1'; end add_one;
  select add_one(level) as call_scalar from dual connect by level < 4; /
  
  This returns the following result successfully:
  
  BANNER_FULL
  ——————————————————————————–
  Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
  Version 19.28.0.1.0
  
  CALL_SCALAR
  ———–
  2
  3
  4
  
  Loading…
  
  1. Anthony Harper
    
    August 13, 2025
    
    All,
    I just checked again and this still works in OCI ADB 19.28.
    Try the follwing test:
    
    with function scalar_upper(s in varchar2) return varchar2 sql_macro(scalar) is begin return 'upper(s)'; end scalar_upper; select scalar_upper(banner_full) as upper_version from v$version /
    
    UPPER_VERSION
    ——————————————————————————–
    ORACLE DATABASE 19C ENTERPRISE EDITION RELEASE 19.0.0.0.0 – PRODUCTION
    VERSION 19.28.0.1.0
    
    Anthony
    
    Loading…

Practical PL/SQL

Leave a ReplyCancel reply