SQL Macros in the Oracle Database

This blog is part of the overall "10 Oracle Database Features That Fly Under the Radar" and explains what SQL Macros are and gives some practical examples.

SQL Macros were made available in version 21c and have been back ported to 19.6. The demo below was setup and run on my free 23ai Autonomous Database.

What are SQL Macros

I'll start with an example. You're working on a sales reporting system and have to calculate employees commission in a bunch of different queries. Maybe they are simple queries but you've got to keep reusing them.

select 
  employee_name,
  sales_amount,
  sales_amount * 0.05 as commission
from sales;

select 
  department,
  sum(sales_amount) as total_sales,
  sum(sales_amount * 0.05) as total_commission
from sales
group by department; 

You say, "I should make this a function!" So you try:

create or replace function calculate_commission(p_amount number)
  return number
is
begin
  return p_amount * 0.05;
end;
/

and you'd be right to do so. This works fine. But, every time you use a function, Oracle has to switch between SQL and PL/SQL modes, which can slow down the system when you're dealing with lots of rows.

Enter SQL Macros

This is where SQL Macros come in. Think of them as reusable code snippets that get added into the SQL statement at runtime. Here's how you'd write the same commision calc from above as a SQL Macro

-- First, create some sample data
create table sales (
  sale_id number primary key,
  employee_name varchar2(100),
  sales_amount number(10,2),
  sale_date date
);

-- Add some test data. This uses the new table value syntax in 23ai for inserting multiple values in one statement.  
insert into sales values (1, 'Alice', 1000, sysdate),
						(2, 'Bob', 2000, sysdate),
 						(3, 'Charlie', 1500, sysdate);

-- Create the commission calc macro
create or replace function calculate_commission(p_amount number)
  return varchar2 sql_macro(scalar)
is
begin
  return q'{
    case
      when p_amount <= 1000 then p_amount * 0.05
      when p_amount <= 2000 then p_amount * 0.06
      else p_amount * 0.07
    end
  }';
end;
/

Now we can use it in our SQL:

select 
  employee_name,
  sales_amount,
  calculate_commission(sales_amount) as commission
from sales;

Whats actually happening?

When you run this query, Oracle doesn't actually call a function. Instead, it replaces calculate_commission(sales_amount) with the actual calculation before running the query. It's as if you wrote:

select 
  employee_name,
  sales_amount,
  case
    when sales_amount <= 1000 then sales_amount * 0.05
    when sales_amount <= 2000 then sales_amount * 0.06
    else sales_amount * 0.07
  end as commission
from sales;

We can see this by looking at the execution plan:

explain plan for
select 
  employee_name,
  sales_amount,
  calculate_commission(sales_amount) as commission
from sales;

select * from table(dbms_xplan.display);

Notice there's no function call in the plan - the macro executed inline

Let's Try Something More Interesting

What if we want to track sales trends? Create a macro that calculates the seasonal adjustments directly:

create or replace function seasonal_sales(
  p_date date, 
  p_amount number
) return varchar2 sql_macro(scalar)
is
begin
  return q'{
    p_amount * 
    case extract(month from p_date)
      when 12 then 1.1  -- December bonus
      when 1 then 0.9   -- January slowdown
      else 1.0
    end
  }';
end;
/

-- Now we can use it
select 
  employee_name,
  sale_date,
  sales_amount,
  seasonal_sales(sale_date, sales_amount) as adjusted_amount
from sales;

SQL Macros: Table Expressions

So far, we've been working with scalar SQL Macros, let's talk about the other type: Table Expression SQL Macros. These let you create reusable table-like structures that are used in FROM clauses.

Table Expression Basics

While scalar macros work with single values, table macros work with entire result sets. Let's add table macros to our sales system:

alter table sales add region varchar2(20);

-- Update our test data with regions
update sales set region = 'EAST' where employee_name = 'Alice';
update sales set region = 'WEST' where employee_name = 'Bob';
update sales set region = 'EAST' where employee_name = 'Charlie';
commit;

-- Create a table macro for regional sales analysis
create or replace function regional_performance 
  return varchar2 sql_macro(table)
is
begin
  return q'{
    select 
      region,
      count(*) as total_sales,
      sum(sales_amount) as revenue,
      avg(sales_amount) as avg_sale,
      min(sales_amount) as smallest_sale,
      max(sales_amount) as largest_sale
    from sales
    group by region
  }';
end;
/

Now we can use the Table Macro like a table

select * from regional_performance();

Improving the Macro

Let's add parameters to make our table macro more versatile:

create or replace function regional_performance(
  p_min_amount in number default 0,
  p_date_from in date default null,
  p_date_to in date default null
) return varchar2 sql_macro(table)
is
begin
  return q'{
    select 
      region,
      count(*) as total_sales,
      sum(sales_amount) as revenue,
      avg(sales_amount) as avg_sale,
      min(sales_amount) as smallest_sale,
      max(sales_amount) as largest_sale
    from sales
    where sales_amount >= p_min_amount
    and (p_date_from is null or sale_date >= p_date_from)
    and (p_date_to is null or sale_date <= p_date_to)
    group by region
  }';
end;
/

No we can use the Macro with different parameters:

-- All sales over $1700
select * from regional_performance(1700);

-- Sales in a date range
select * from regional_performance(
  p_min_amount => 0,
  p_date_from => date '2023-01-01',
  p_date_to => date '2029-12-31'
);

And we can check the execution plan

explain plan for
select * from regional_performance(1700);

select * from table(dbms_xplan.display);

Notice how Oracle optimizes this just like a regular query and no function call overhead.

Combining Table and Scalar Macros

We can also use our table macro with our scalar commission macro:

select 
  r.*,
  calculate_commission(r.avg_sale) as avg_commission
from regional_performance() r;

Tips for Table Macros

1. Start Simple: Start with basic grouping and filtering before getting complex

2. Use Parameters: Make your macros flexible with parameters

3. Add Validation: Consider adding parameter validation

4. Document Dependencies: Note which tables your macro depends on

Remember, SQL Macros are about making your code both maintainable AND performant. They're a nice tool when you need to create standardized ways of analyzing your data.

Heres the documentation to learn more: SQL Macros in the Oracle Database

-Killian