SQL macros: a query with parameters

A view can't take parameters: you can't write select * from top_scorers_v(2026, 5). A SQL macro can. It's a PL/SQL function that returns the text of a query; Oracle pastes that text into your statement when it's parsed, so it runs as plain SQL, with no switching between SQL and PL/SQL for every row (which is what makes ordinary PL/SQL functions in queries slow).

• Table macros (SQL_MACRO(TABLE), 19c from 19.6) go in the FROM clause, like a view with parameters.
• Scalar macros (SQL_MACRO(SCALAR), 21c and later, so 26ai) go anywhere an expression goes, like a reusable formula.

Test 1: a view with parameters

The top n run-scorers of a season, as something you can select from: here 2026 and 5.

Setup

create or replace function d144_top_scorers (p_season number, p_n number)
return varchar2 sql_macro(table) is
begin
  return q'{
    select p.player_name, sum(b.runs_batter) as runs,
           sum(case when b.extra_type = 'WD' then 0 else 1 end) as balls
    from   cr_balls_v b
    join   cr_players p on p.player_id = b.batter_id
    where  b.season = p_season
    group  by p.player_name
    order  by runs desc, p.player_name
    fetch  first p_n rows only }';
end;
/

The function returns a string. Inside it, p_season and p_n are the parameter names: Oracle replaces them with whatever you pass in. No string concatenation, so no SQL injection.

Way 1: the table macro

select * from d144_top_scorers(2026, 5);

That's the whole query. d144_top_scorers(2025, 3) gives 2025's top three, from the same function.

Way 2: the plain query

select p.player_name, sum(b.runs_batter) as runs,
       sum(case when b.extra_type = 'WD' then 0 else 1 end) as balls
from   cr_balls_v b
join   cr_players p on p.player_id = b.batter_id
where  b.season = 2026
group  by p.player_name
order  by runs desc, p.player_name
fetch  first 5 rows only;

What the macro expands into. It's fine once; it's a problem when it's copied into twelve reports and someone fixes a bug in only nine of them.

Test 2: a reusable formula (26ai)

The same top five, with each batter's strike rate from a scalar macro.

Setup

create or replace function d144_strike_rate (p_runs number, p_balls number)
return varchar2 sql_macro(scalar) is
begin
  return q'{ round(100 * p_runs / nullif(p_balls, 0), 1) }';
end;
/

Way 1 (26ai): a scalar macro

26ai only

select player_name, runs, balls, d144_strike_rate(runs, balls) as strike_rate
from   d144_top_scorers(2026, 5);

Two macros working together: the table macro supplies the rows, the scalar macro the formula. Both disappear at parse time, leaving one plain SQL statement.

Way 2 (19c): WITH FUNCTION

with
  function strike_rate(p_runs number, p_balls number) return number is
  begin
    return round(100 * p_runs / nullif(p_balls, 0), 1);
  end;
select player_name, runs, balls, strike_rate(runs, balls) as strike_rate
from   d144_top_scorers(2026, 5)
/

On 19c, WITH FUNCTION (page 4.3) is the nearest thing: a real PL/SQL function, local to the query, called once per row.

19c and 26ai

Table macros: 19c (19.6 and later) and 26ai. Scalar macros: 26ai (21c and later).

Gotchas

• The macro runs once, at parse time, not per row. Anything it computes (sysdate, a lookup) is fixed into the SQL text. Return SQL that does the work, not values.
• To see the SQL a macro produced, use DBMS_UTILITY.EXPAND_SQL_TEXT on the calling query.
• Parameters are substituted as values, not as text: you can't pass a column name or a table name as a VARCHAR2 and have it become part of the query. Table parameters (DBMS_TF.TABLE_T) are the way to pass a table in.