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 dropped into the SQL statement at runtime. Here's how you'd write the same commision statement as a SQL Macro
-- First, let's 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
insert into sales values (1, 'Alice', 1000, sysdate),
(2, 'Bob', 2000, sysdate),
(3, 'Charlie', 1500, sysdate);
-- Create our commission calculation 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 has been executed inline.
Let's Try Something More Interesting
What if we want to track sales trends? We can create a macro that 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 explore the other type: Table Expression SQL Macros. These let us create reusable table-like structures that we can use in our FROM clauses.
Table Expression Basics
While scalar macros work with single values, table macros work with entire result sets. Let's enhance our sales reporting system with some table macros:
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(100);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 $1500
select * from regional_performance(1500);
-- 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(1500);
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
Start Simple: Begin with basic grouping and filtering before getting complex
Use Parameters: Make your macros flexible with parameters
Add Validation: Consider adding parameter validation where needed
Document Dependencies: Note which tables your macro depends on
Remember, SQL Macros are about making your code both maintainable AND performant. They're a powerful 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
Comments