Introduction

In this post we will cover another database feature not which is unjustly ignored:

Oracle SQL Macros are one of the most interesting and least known modern Oracle SQL features.
Although introduced in Oracle 19c and improved in Oracle 21c and 23ai, many Oracle professionals still do not use them!

SQL Macros allow developers to generate SQL dynamically at parse time without relying on traditional dynamic SQL.

This makes SQL Macros:

• powerful,
• optimizer-friendly,
• reusable,
• and surprisingly elegant.

In this article we will explore:

• SCALAR SQL Macros,
• TABLE SQL Macros,
• practical use cases,
• performance considerations,
• and common pitfalls.

I hope that all examples are simple and easy to reproduce.

What Are Oracle SQL Macros?

Basically, a SQL Macro is a PL/SQL function that returns SQL text.

However, Oracle does not execute the function like a normal PL/SQL function.

Instead:

1. Oracle expands the returned SQL text,
2. injects it into the final SQL statement,
3. and optimizes the resulting query normally.

Think of SQL Macros as:
• reusable SQL templates,
• parameterized views,
• or compile-time query generators.

Why SQL Macros Matter

Traditional approaches often have limitations:

SQL Macros solve many of these problems elegantly.

SQL Macro Types

Oracle supports two SQL Macro types:

Requirements

SQL Macros require:
• Oracle 19c (limited support),
• Oracle 21c,
• Oracle 23ai.
The feature is significantly more mature in Oracle 21c and later.

Demo Environment

Create a simple table for testing.

CREATE TABLE employees_demo2 (
    employee_id NUMBER,
    first_name  VARCHAR2(50),
    last_name   VARCHAR2(50),
    department  VARCHAR2(50),
    salary      NUMBER
);

Insert sample data:

INSERT INTO employees_demo2 VALUES (1,'John','Smith','IT',7000);
INSERT INTO employees_demo2 VALUES (2,'Sarah','Connor','HR',6000);
INSERT INTO employees_demo2 VALUES (3,'Mike','Taylor','IT',9000);
INSERT INTO employees_demo2 VALUES (4,'Emma','Brown','Sales',5000);
COMMIT;

SCALAR SQL Macros

SCALAR SQL Macros generate SQL expressions.

They are useful for:

• reusable calculations,
• formatting,
• conditional logic,
• standardized expressions.

Example 1 – Simple Name Formatter

Create a SCALAR SQL Macro:

CREATE OR REPLACE FUNCTION full_name(
    p_first_name VARCHAR2,
    p_last_name  VARCHAR2
)
RETURN VARCHAR2
SQL_MACRO(SCALAR)
IS
BEGIN
    RETURN q'[
        UPPER(p_first_name || ' ' || p_last_name)
    ]';
END;
/

Use it like a normal SQL function.

SELECT
    employee_id,
    full_name(first_name, last_name) AS full_name
FROM employees_demo;

Output:

MPLOYEE_ID FULL_NAME                                                                                            
----------- -----------------------------------------------------------------------------------------------------
          1 JOHN SMITH                                                                                           
          2 SARAH CONNOR                                                                                         
          3 MIKE TAYLOR                                                                                          
          4 EMMA BROWN

What Actually Happens?

Oracle internally expands the SQL.

The query becomes approximately:

SELECT
    employee_id,
    UPPER(first_name || ' ' || last_name)
FROM employees_demo2;

This is important because:

• the optimizer can fully understand the query,
• execution plans remain efficient,
• and there is no PL/SQL context switching overhead!

Example 2 – Reusable Salary Classification

This is a realistic enterprise use case.

CREATE OR REPLACE FUNCTION salary_grade(
    p_salary NUMBER
)
RETURN VARCHAR2
SQL_MACRO(SCALAR)
IS
BEGIN
    RETURN q'[
        CASE
            WHEN p_salary < 6000 THEN 'LOW'
            WHEN p_salary < 8000 THEN 'MEDIUM'
            ELSE 'HIGH'
        END
    ]';
  END;

Usage:

SELECT
    first_name,
    salary,
    salary_grade(salary) AS salary_category
FROM employees_demo;

Why SCALAR SQL Macros Are Interesting

SCALAR SQL Macros:
• centralize business logic,
• reduce duplicated CASE expressions,
• improve readability,
• avoid PL/SQL execution overhead.

This is especially useful in:
• reporting systems,
• BI queries,
• data warehouse transformations,
• and enterprise applications.

TABLE SQL Macros

TABLE SQL Macros are much more powerful.!
They generate entire SQL query blocks dynamically.
Think of them as:
• parameterized views,
• reusable query frameworks,
• SQL generators.

Example 3 – Parameterized Department Filter

Create a TABLE SQL Macro.

CREATE OR REPLACE FUNCTION employees_by_department(
    p_department VARCHAR2
)
RETURN VARCHAR2
SQL_MACRO(TABLE)
IS
BEGIN
    RETURN '
        SELECT
            employee_id,
            first_name,
            last_name,
            department,
            salary
        FROM employees_demo2
        WHERE department = ''' || p_department || '''
    ';
END;
/

Usage:

SELECT *
FROM employees_by_department('IT');

Why TABLE SQL Macros Are Powerful

Normally:
• views cannot accept parameters,
• and dynamic SQL becomes messy quickly.

TABLE SQL Macros provide reusable parameterized SQL logic cleanly.

Example 4 – Generic Top-N Query

A practical reporting example.

SELECT *
FROM employees_by_department('IT');
CREATE OR REPLACE FUNCTION top_n_salaries(
    p_limit NUMBER
)
RETURN VARCHAR2
SQL_MACRO(TABLE)
IS
BEGIN
    RETURN q'[
        SELECT
            employee_id,
            first_name,
            salary
        FROM employees_demo2
        ORDER BY salary DESC
        FETCH FIRST p_limit ROWS ONLY
    ]';
END;
/

Usage:

SELECT *
FROM top_n_salaries(2);

Output:

EMPLOYEE_ID FIRST_NAME                                             SALARY
----------- -------------------------------------------------- ----------
          3 Mike                                                     9000
          1 John                                                     7000

Example 5 – Dynamic Date Range Filtering

This pattern is extremely useful in reporting systems.

CREATE TABLE orders_demo (
    order_id NUMBER,
    customer_name VARCHAR2(50),
    order_date DATE,
    amount NUMBER
);
  

Insert test data:

INSERT INTO orders_demo VALUES (1,'Alice',DATE '2025-01-10',120);
INSERT INTO orders_demo VALUES (2,'Bob',DATE '2025-02-15',300);
INSERT INTO orders_demo VALUES (3,'Charlie',DATE '2025-03-20',450);
COMMIT;

Create the macro.

CREATE OR REPLACE FUNCTION orders_between_dates(
    p_start_date DATE,
    p_end_date   DATE
)
RETURN VARCHAR2
SQL_MACRO(TABLE)
IS
BEGIN
    RETURN q'[
        SELECT *
        FROM orders_demo
        WHERE order_date BETWEEN p_start_date AND p_end_date
    ]';
END;
/

Usage:

SELECT *
FROM orders_between_dates(
    DATE '2025-01-01',
    DATE '2025-02-28'
);

Performance Benefits

One major advantage of SQL Macros is optimizer visibility.

Unlike some PL/SQL functions:
• SQL Macros are expanded before optimization,
• allowing Oracle to generate efficient execution plans.

This often performs better than:
• traditional PL/SQL functions,
• pipelined functions,
• or dynamic SQL frameworks.

Important Limitations

SQL Macros are powerful, but not perfect.

Parse-Time Expansion

Macros are expanded during SQL parsing.
This means:
• behavior differs from runtime PL/SQL logic,
• some dynamic scenarios may not work as expected.

Debugging Can Be Confusing

Errors sometimes appear in the expanded SQL rather than the macro itself.
Complex macros can become difficult to troubleshoot.

Security Considerations

Avoid unsafe string concatenation patterns.
Even though macros are safer than raw dynamic SQL, careless implementations can still introduce risks

Tooling Support Is Still Limited

Some IDEs and SQL formatters:
• do not fully understand SQL Macros,
• or display confusing syntax warnings.

SQL Macros vs Views

SQL Macros vs PL/SQL Functions

When SQL Macros Work Best

SQL Macros are excellent for:
• reporting frameworks,
• reusable filtering logic,
• parameterized queries,
• standardized calculations,
• security filtering,
• reusable analytics logic.

When NOT to Use SQL Macros

Avoid SQL Macros when:
• logic becomes overly complicated,
• runtime procedural behavior is required,
• dynamic object names are needed extensively,
• maintainability suffers.

Final Thoughts

SQL Macros are one of Oracle’s most elegant modern SQL features.
Unfortunately, they remain underused despite solving several long-standing Oracle SQL challenges.
The biggest advantages are:
• reusable SQL,
• parameterized query generation,
• optimizer-friendly execution,
• and cleaner enterprise SQL design.
For advanced Oracle developers and DBAs, SQL Macros are definitely worth learning.
Especially in Oracle 23ai environments, SQL Macros can significantly improve:
• code quality,
• maintainability,
• and query design.

If you are not already using SQL Macros in your Oracle environment, now is an excellent time to start experimenting with them!