How to Write Table SQL Macros in Oracle

A table SQL macro returns the text of a query, and can be used in the FROM clause like a table. Its parameters can be values, making it a parameterized view, or even tables and column lists, making it a reusable query shape for any table. Oracle expands the text into the calling query when it is parsed.

Code for This Guide

The main examples are in the examples/advanced-plsql folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create [or replace] function name (p_table dbms_tf.table_t,
                                   p_columns dbms_tf.columns_t, p_value type)
  return varchar2 sql_macro(table)
is
begin
  return 'select ... from p_table ...';
end;

select ... from name(table_name, columns(column_name), value);

DBMS_TF.TABLE_T receives a table, and DBMS_TF.COLUMNS_T a list of columns written with the COLUMNS operator. Ordinary parameters such as numbers and strings work too.

A Parameterized View

ROUTES_FROM takes an origin airport and returns the routes from it, like a view that accepts an argument.

Example:

create or replace function routes_from (p_origin varchar2)
  return varchar2 sql_macro(table)
is
begin
  return 'select route_id, origin, destination, distance_km
          from   routes
          where  origin = p_origin';
end;
/
-- a parameterized view: the parameter is bound, not concatenated
select * from routes_from('LHR') order by route_id;

select count(*) as from_sin from routes_from('SIN');

Output:

Function ROUTES_FROM compiled

   ROUTE_ID ORIGIN    DESTINATION       DISTANCE_KM
___________ _________ ______________ ______________
          2 LHR       DXB                      5497
         41 LHR       JFK                      5540

   FROM_SIN
___________
          3

The parameter name in the returned text becomes the argument, so routes_from('LHR') returns the two routes from London. The example drops the function afterward.

One Query for Any Table

TOP_N takes a table, a column, and a count, and returns the top rows by that column. The same macro works for airports by elevation and employees by salary.

Example:

create or replace function top_n (p_table dbms_tf.table_t, p_column dbms_tf.columns_t,
                                  p_n number)
  return varchar2 sql_macro(table)
is
begin
  return 'select * from p_table order by ' || p_column(1)
         || ' desc fetch first p_n rows only';
end;
/
select airport_code, elevation_ft from top_n(airports, columns(elevation_ft), 3);

select last_name, salary from top_n(employees, columns(salary), 2);

Output:

Function TOP_N compiled

AIRPORT_CODE       ELEVATION_FT
_______________ _______________
JNB                        5558
NBO                        5327
KTM                        4390

LAST_NAME         SALARY
______________ _________
Haddad             48000
Al Mansoori        36000

The column name from COLUMNS is built into the text, while the table and the row count are put in by name. The example drops both TOP_N and MINUTES_BETWEEN, a scalar macro from the same repository folder.

Things to Know

• The function runs at parse time only; the expanded query is optimized and run like any other SQL.
• Do not build the text from string values passed in; refer to them by parameter name so they stay values.
• Table macros can be used only in the FROM clause.

Related Guides

• Oracle Pipelined Function Example
• How to Write Scalar SQL Macros in Oracle

Conclusion

A table SQL macro is a function marked SQL_MACRO(TABLE) that returns a query, used in FROM like a table. Use value parameters for parameterized views and DBMS_TF table and column parameters for reusable query shapes across tables.