I Ignored SQL Macros for Two Years. A Pagination Refactor Changed That.

How a boring pagination refactor finally taught me what this feature is actually for.

I’d been hearing about SQL Macros for a while — the usual conference-talk enthusiasm, the “Oracle finally lets you parameterize a view!” posts, the demos with TOP_N and hash-diff generators. I filed it away as one of those features evangelists love and production codebases quietly ignore. My mental model, if I'm honest, was something like: dynamic SQL with a seatbelt — a query-rewriter trick with some injection-guarding sprinkled on top. Interesting, not urgent. I kept postponing it.

This week I finally had a use case that wasn’t a demo. And it turned out my mental model was wrong in a way that actually matters.

The itch

Across a family of PL/SQL functions that build JSON API responses, I had the same pagination boilerplate copy-pasted everywhere:

sql

select qq.*, ROW_NUMBER() OVER (ORDER BY 1) ORD_RN
from (
    select * from tb_students where ...
) qq

sql

where ord_rn between (l_num_rows * (l_pagenum-1) + 1)
                  and (l_num_rows * (l_pagenum-1) + l_num_rows)

Nothing wrong with it, functionally. But it was duplicated across every paginated query, with two local variables (l_pagenum, l_num_rows) fetched from session context in every function that needed it. The kind of thing that's fine until someone changes the pagination logic six months from now and has to hunt down every copy.

I wanted one place to own this. And — deliberately — I wanted it done in static SQL. No EXECUTE IMMEDIATE, no DBMS_SQL, no "just build the string and hope." I've been burned enough times by dynamic SQL's blast radius in a banking codebase to actively avoid it unless there's no other way.

That constraint is exactly what pushed me toward SQL Macros for the first time. Not evangelism — a specific, boring need for centralization without giving up static SQL.

What actually happens under the hood

Here’s the thing that reframed it for me: a SQL Macro isn’t executed at runtime at all. It’s a PL/SQL function that runs once, at hard-parse time, and returns a string. That string is spliced into the calling query before the optimizer ever sees it. The result is one single, fully-inlined statement — explainable, traceable, optimized as a whole, exactly as if you’d hand-written it yourself.

That’s not a rewriter trick sitting in front of your query at runtime. It’s closer to what the feature is actually named in a few blog posts I found afterward: a parameterized view. Views can’t take parameters — you can’t write SELECT * FROM my_view(:x). A table SQL Macro is Oracle's answer to that specific, long-standing gap.

The central piece I landed on:

sql

create or replace function fn_paginate(
    p_tab dbms_tf.table_t
) return varchar2 sql_macro(table)
is
begin
    return q'{
        select qq.*
        from (
            select t.*, row_number() over (order by 1) ord_rn
            from p_tab t
        ) qq
        where qq.ord_rn between
              (global.pagesize * (global.pagenum - 1) + 1)
          and (global.pagesize * (global.pagenum - 1) + global.pagesize)
    }';
end fn_paginate;
/

And every consumer collapses to this:

sql

for i in (
    with qry as (
        select * from tb_students where ...
    )
    select * from fn_paginate(qry)
)
loop
    ...

One function owns the pagination logic. Every caller writes a plain, static query and hands it in. No repeated ROW_NUMBER wrapping, no repeated bounds arithmetic, no dynamic SQL anywhere.

The gotchas that actually taught me something

None of this was frictionless, and the friction is where the real learning was.

You can’t just pass a subquery. My first instinct was fn_paginate(cursor(select * from tb_students where ...)) — completely reasonable if you're thinking of this as a function call. It fails with PLS-00306: wrong number or types of arguments, and the reason is very specific: a TABLE_T argument has to be an actual table, view, or a name defined in a preceding WITH clause — not a raw subquery, not CURSOR(...). So the call becomes:

sql

with qry as (
    select * from tb_students where ...
)
select * from fn_paginate(qry)

I’ll admit — naming a query just to hand its name to a function genuinely made me laugh out loud the first time I got it working. It felt like an oddly formal handshake for something so simple. But once I understood why — the macro parameter has to resolve to something the parser can textually splice in, not an anonymous expression — it stopped feeling arbitrary.

Classic parameters are name-substituted, not value-evaluated. I tried parameterizing the pagination bounds (p_pagenum, p_pagesize) and, separately, tried calling my session-context getters (global.pagenum(), global.pagesize()) directly from inside the macro body. Both work, but for a reason that took some sitting with: at parse time, a variable argument's value is null inside the macro — only its name is visible, and that name gets dropped into the returned text verbatim, to be resolved as a real bind or function call when the final statement actually executes. You're not reading data inside the macro; you're assembling a template.

Modernizing to OFFSET/FETCH looked like a clean win — and then wasn't. I tried swapping the old ROW_NUMBER/BETWEEN pattern for the more current row-limiting clause:

sql

offset (global.pagesize() * (global.pagenum() - 1)) rows
fetch next global.pagesize() rows only

Straight into ORA-62550: Invalid SQL ROW LIMITING expression. Turns out OFFSET/FETCH only accepts literals, binds, correlation variables, or subqueries in those clauses — a bare function call doesn't qualify, even though the identical call works fine inside a plain WHERE ... BETWEEN. Wrapping it in a scalar subquery satisfied the grammar:

sql

offset (select global.pagesize() * (global.pagenum() - 1) from dual) rows

That compiled and ran cleanly in isolation. Then it broke in the actual API call path with ORA-01007: Reference to a variable not in SELECT clause — a mismatch that only showed up once the query passed through my framework's dynamic execution layer, not in a plain SQL client. The old ROW_NUMBER/BETWEEN version, meanwhile, has been rock solid through the same path the whole time.

So I kept it. Not because OFFSET/FETCH is wrong — it's genuinely nicer syntax — but because the centralization win was never about which pagination technique sits inside the macro. It was about there being exactly one place it lives. ROW_NUMBER/BETWEEN inside a macro is still infinitely better than ROW_NUMBER/BETWEEN copy-pasted eleven times.

What changed my mind

I went in assuming this was syntactic sugar over dynamic SQL with some injection-safety story attached. It isn’t. There’s no runtime string execution at all in the artifact you ship — the macro does its work once, at compile time, and disappears. What’s left is a single, ordinary, fully static SQL statement that the optimizer treats no differently than if you’d typed the whole thing by hand.

The honest caveats are real, and worth carrying into any adoption decision:

• Table arguments must be named (table, view, or a preceding WITH alias) — no anonymous subqueries or CURSOR(...).
• Macros can’t be invoked from inside a WITH clause, though they can consume one.
• Row-limiting clauses have a narrower grammar than WHERE predicates — a working pattern in one clause type isn't guaranteed to transplant cleanly into another.
• If your execution stack has any layer built around DBMS_SQL (ad-hoc script runners, generic API dispatchers, some ORDS-style plumbing), test the macro-expanded query through that actual path, not just a plain SQL client. I hit two separate issues that only manifested once real framework code was in the loop.

None of that makes it a toy. It makes it a feature with a real, learnable shape — which is a different thing entirely from “rewriter trick.” I’m glad I finally had a boring enough problem to stop postponing it.

If you’ve hit your own SQL Macro gotchas — especially anything involving DBMS_TF.COLUMNS_T for multi-column ordering, or performance comparisons against the classic ROWNUM approach — I'd genuinely like to compare notes.