SQL Macros in 21c: Writing Reusable SQL Fragments as Functions

A New Kind of Reuse in SQL

SQL macros, introduced in Oracle 21c, let you write PL/SQL functions that return SQL fragments which the optimizer inlines directly into the calling SQL statement. Unlike traditional PL/SQL functions called from SQL, SQL macros are expanded at parse time, so the optimizer sees the expanded SQL and can fully optimize it as if you had written the SQL inline.

Scalar SQL Macros

CREATE OR REPLACE FUNCTION full_name(
    p_first VARCHAR2,
    p_last  VARCHAR2
) RETURN VARCHAR2 SQL_MACRO(SCALAR)
IS
BEGIN
    RETURN q'[ INITCAP(p_first) || ' ' || INITCAP(p_last) ]';
END;
/

-- The optimizer replaces full_name() with the inline expression
SELECT employee_id,
       full_name(first_name, last_name) AS display_name
  FROM employees;

There is no context switch. The optimizer replaces the macro call with the SQL expression at compile time, so it can participate in index usage, predicate pushing, and all other optimizer transformations.

Table SQL Macros

Table SQL macros return a SQL fragment that produces rows and can be used in the FROM clause:

CREATE OR REPLACE FUNCTION active_employees_in_dept(
    p_dept_id NUMBER
) RETURN VARCHAR2 SQL_MACRO(TABLE)
IS
BEGIN
    RETURN q'[
        SELECT employee_id, first_name, last_name, salary, hire_date
          FROM employees
         WHERE department_id = p_dept_id
           AND status = 'ACTIVE'
           AND termination_date IS NULL
    ]';
END;
/

SELECT * FROM active_employees_in_dept(20);

SELECT ae.first_name, d.department_name
  FROM active_employees_in_dept(20) ae
  JOIN departments d ON d.department_id = 20;

Why This Matters

Before SQL macros, you had two choices for SQL reuse: views (static, no parameters) or PL/SQL functions (parameterized, but context switching). SQL macros combine the best of both: parameterized like functions and optimized like inline SQL.

Limitations

SQL macros return text, not results. The returned text must be pure SQL with no procedural logic. The macro function runs at parse time, not execution time, so it cannot reference session state or runtime values. In Oracle 23ai, SQL macros have been enhanced with support for default parameter values and improved error messages.