SQL Macros and Reusable SQL Abstractions, Optimizer Visibility, and Maintainability

Learning outcomes

ServiceHub repeats the same priority classification and open-work-order filtering in dashboards, APIs, and ad-hoc SQL. Copy/paste drifts, while wrapping everything in opaque procedural or dynamically constructed SQL can hide predicates from review and create injection risk. SQL macros provide reusable SQL text that is expanded into the calling statement, preserving optimizer visibility—but their privilege model and restrictions must be understood.

1. What a SQL macro is

A SQL macro is a PL/SQL function whose return value is SQL text. When called from SQL, Oracle expands that text into the calling statement. The macro function itself must return VARCHAR2, CHAR, or CLOB. TABLE is the default macro kind; SCALAR must be declared explicitly.

CREATE OR REPLACE FUNCTION sh_priority_label(p_priority NUMBER)  RETURN VARCHAR2 SQL_MACRO(SCALAR)ISBEGIN  RETURN q'{    CASE      WHEN p_priority >= 90 THEN 'CRITICAL'      WHEN p_priority >= 60 THEN 'HIGH'      ELSE 'NORMAL'    END  }';END;/

SELECT work_order_id,       priority_score,       sh_priority_label(priority_score) AS priority_labelFROM servicehub_sql_macro_woORDER BY work_order_id;

2. Table macros are parameterized table expressions

CREATE OR REPLACE FUNCTION sh_open_work_orders(p_min_cost NUMBER)  RETURN VARCHAR2 SQL_MACRO(TABLE)ISBEGIN  RETURN q'{    SELECT work_order_id, asset_id, status_code, estimated_cost, priority_score    FROM servicehub_sql_macro_wo    WHERE status_code = 'OPEN'      AND estimated_cost >= p_min_cost  }';END;/

SELECT *FROM sh_open_work_orders(500)ORDER BY estimated_cost DESC, work_order_id;

The formal parameter name appears in the returned SQL text. Oracle binds the macro call’s argument into the expanded expression; do not concatenate untrusted user strings into returned SQL.

3. Deliberately wrong: call a table macro as a scalar function

SELECT sh_open_work_orders(500)FROM dual;-- ORA-64629: table SQL macro can only appear in FROM clause of a SQL statement

The repair is structural: table macros belong in the FROM clause. Conversely, a scalar macro cannot be used as a table expression. These are different contracts, not interchangeable wrappers.

4. Observe that Oracle knows the object is a macro

SELECT object_name,       procedure_name,       sql_macroFROM user_proceduresWHERE object_name IN ('SH_PRIORITY_LABEL','SH_OPEN_WORK_ORDERS')ORDER BY object_name, procedure_name;

Current 26ai dictionary metadata reports SCALAR or TABLE for SQL macro units. This proves object type metadata; it does not prove that a particular query uses the plan you expect.

EXPLAIN PLAN SET STATEMENT_ID='SH06L5'FORSELECT work_order_id, asset_id, estimated_costFROM sh_open_work_orders(500)WHERE asset_id = 10;SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,'SH06L5','BASIC +PREDICATE'));

Inspect predicate information: the expanded SQL exposes normal relational predicates to optimization. Exact access paths remain data/statistics-dependent.

5. Security is subtle: construction rights and evaluation rights differ

Oracle documents that AUTHID cannot be specified for a SQL macro. The macro function body executes with definer’s rights while constructing the SQL text, but the resulting SQL expression is evaluated with invoker’s rights. A macro referenced in a view is processed with the view owner’s privileges. These boundaries mean a macro is not a generic privilege-escalation wrapper.

6. Macros versus views, PL/SQL functions, and dynamic SQL

7. Hands-on lab

DROP TABLE servicehub_sql_macro_wo IF EXISTS PURGE;CREATE TABLE servicehub_sql_macro_wo (  work_order_id NUMBER PRIMARY KEY,  asset_id NUMBER NOT NULL,  status_code VARCHAR2(20) NOT NULL,  estimated_cost NUMBER(12,2) NOT NULL,  priority_score NUMBER NOT NULL);INSERT INTO servicehub_sql_macro_wo VALUES (601,10,'OPEN',900,95);INSERT INTO servicehub_sql_macro_wo VALUES (602,10,'OPEN',300,65);INSERT INTO servicehub_sql_macro_wo VALUES (603,20,'CLOSED',800,70);INSERT INTO servicehub_sql_macro_wo VALUES (604,20,'OPEN',550,40);COMMIT;

CREATE OR REPLACE FUNCTION sh_priority_label(p_priority NUMBER)  RETURN VARCHAR2 SQL_MACRO(SCALAR)ISBEGIN  RETURN q'{CASE WHEN p_priority >= 90 THEN 'CRITICAL'                  WHEN p_priority >= 60 THEN 'HIGH'                  ELSE 'NORMAL' END}';END;/CREATE OR REPLACE FUNCTION sh_open_work_orders(p_min_cost NUMBER)  RETURN VARCHAR2 SQL_MACRO(TABLE)ISBEGIN  RETURN q'{SELECT work_order_id, asset_id, status_code,                   estimated_cost, priority_score            FROM servicehub_sql_macro_wo            WHERE status_code = 'OPEN'              AND estimated_cost >= p_min_cost}';END;/

SELECT work_order_id,       sh_priority_label(priority_score) AS priority_labelFROM sh_open_work_orders(500)ORDER BY work_order_id;SELECT object_name, sql_macroFROM user_proceduresWHERE object_name IN ('SH_PRIORITY_LABEL','SH_OPEN_WORK_ORDERS')ORDER BY object_name;

DROP FUNCTION sh_open_work_orders;DROP FUNCTION sh_priority_label;DROP TABLE servicehub_sql_macro_wo PURGE;

8. Production judgment and chapter bridge

Use macros when reuse benefits from being expressed as visible SQL rather than hidden procedural calls. Keep returned SQL static and reviewable, treat privileges as part of the API contract, and test the expanded semantics on every supported database release. Do not add undocumented hints or hidden parameters to force macro behavior. For simple parameter-free reuse, a view may be superior; for procedural logic, an ordinary function/package can be clearer.

Chapter 06 closes with a reusable-query mechanism that preserves relational visibility. Chapter 07 turns to DML, transactions, read consistency, undo, locks, and concurrency—where correctness is no longer only about query result semantics but also about what concurrent sessions can safely change.

Authoritative references

• SQL_MACRO Clause — SCALAR/TABLE syntax, restrictions and privilege semantics
• ALL_PROCEDURES / USER_PROCEDURES metadata — SQL_MACRO metadata column
• PL/SQL Release Changes — 26ai SQL Transpiler context
• SELECT — table-expression usage and SQL macro examples