Since I have written a lot about SQL macros, I have tested some less obvious, sometimes surprising cases. But expanding about them in the original posts would just overload them, so I decided to make a dedicated post for that. The other good reason for this is that the SQL macros are, in my opinion, not documented in enough detail. So additional research and testing can be useful.
TL;DR
If you don’t have time to read the whole story, here are the key findings:
- SQL macros are allowed where PL/SQL functions can be called
- This means not only in SELECT, WHERE, GROUP BY, HAVING, ORDER BY clauses, but also as a part of a FROM clause in a join condition, MATCH_RECOGNIZE, VALUES clause, etc.
- As of now (Oracle 23.3), there is a bug prohibiting the use of scalar macros in GROUP BY clause
- SQL macros are also allowed in DML and even some DDL (CTAS)
- For other DDL such as maintenance operations with filtering or creation of function-based indexes it is disallowed
- Nesting of SQL macros (both scalar and table ones) is not possible at the SQL level
- But you can nest them in the macro body (only one at once)
Where are the scalar SQL Macros allowed?
There are several sections in the documentation explaining where the scalar macros can be called. Here are two quotes from the documentation.
1. SQL macros can either be scalar expressions, typically used in SELECT lists, WHERE, GROUP BY and HAVING clauses, to encapsulate calculations and business logic or can be table expressions, typically used in a FROM clause
2. A
SCALARmacro cannot appear inFROMclause of a query table expression. It can appear wherever PL/SQL functions are allowed, for example in the select list, theWHEREclause, and theORDER BYclause.
This and also the presence of the error ORA-64628: scalar SQL macro cannot appear in FROM clause of a SQL statement suggests that the scalar SQL macros are not allowed in the FROM clause. However, this is not entirely correct. I have studied the syntax diagrams in the SQL Language Reference, trying to find this thing “FROM clause of a query table expression” mentioned in the second quote – without success. After all, I think it should be the other way round: “a query table expression of the FROM clause“
As you can see, the FROM clause consists of, among other things, “table_reference”, which in turn contains “query_table_expression”, but also some other clauses. Only in this “query_table_expression” are the scalar macros disallowed. If we look at other clauses, we’ll see that the rule “it can appear wherever PL/SQL functions are allowed” works pretty well!
All following examples do not pretend to make any sense, but rather just to show what is possible.
Here is an example of using scalar SQL macro in the VALUES clause (table value constructor – 23c)
SQL> select *
2 from (values (1,'A')
3 , (2, upper_macro('b'))
4 ) c (id, name);
ID N
---------- -
1 A
2 B
Listing 1: SQL macro in values clause
And here follows the example of using it in a MATCH_RECOGNIZE clause.
SQL> select *
2 from emp match_recognize
3 (partition by job
4 order by empno
5 measures upper_macro(lower(ename)) as ename
6 pattern(a)
7 define a as (1=1) );
JOB ENAME
--------- ----------
ANALYST SCOTT
ANALYST FORD
CLERK SMITH
CLERK ADAMS
...
Listing 2: scalar SQL macro in MATCH_RECOGNIZE
If we look at the PIVOT clause, the PL/SQL functions are unfortunately not allowed there. The same is valid for UNPIVOT. Therefore no SQL macros either.
SQL> select *
2 from t_emp_kv
3 pivot (any_value(key_val_char) for key_name IN (upper_macro('ENAME'),'JOB'));
Error report -
SQL Error: ORA-56901: non-constant expression in pivot or unpivot values clause
56901. 0000 - "non-constant expression is not allowed for pivot|unpivot values"
Listing 3: no scalar macros in the PIVOT clause
Actually, if the SQL macro returns a literal/constant, then, after macro expansion, the statement would be just fine. But obviously this check takes place before macro expansion, so we get ORA-56901.
Another sub-clauses of the table_reference doesn’t allow for user-defined PL/SQL functions. But besides table_reference there are also join_clause and inline_analytic_view there. What about them?
SQL> create or replace function join_col_par (p_col in varchar2)
2 return varchar2 sql_macro(scalar) is
3 begin
4 return 'round(join_col_par.p_col,-2)';
5 end;
6 /
Function JOIN_COL_PAR compiled
SQL>
SQL> select e1.ename, e1.sal, e2.ename, e2.sal
2 from emp e1 join emp e2
3 on e1.sal = join_col_par(e2.sal)
4 and e1.empno != e2.empno ;
ENAME SAL ENAME SAL
---------- ---------- ---------- ----------
MILLER 1300 WARD 1250
SCOTT 3000 JONES 2975
FORD 3000 JONES 2975
MILLER 1300 MARTIN 1250
FORD 3000 SCOTT 3000
SCOTT 3000 FORD 3000
6 rows selected.
Listing 4: scalar SQL macros in the JOIN clause
As can be seen in the Listing 4, it works in the JOIN clause. I have not tested the inline_analytic_view clause and I also miss the new GRAPH_TABLE operator (23c) on this syntax diagrams, which I leave to you.
A note about GROUP BY
Although using SQL macros in a GROUP BY clause is explicitly mentioned in the documentation, this doesn’t work as of Oracle 23.3 and turns to be a bug.
SQL macros in DML and DDL
In the context of SQL macros, people often only talk about queries. But one can use them in DML statements too:
SQL> update emp
2 set ename = upper_macro(ename);
14 rows updated.
Listing 5: scalar macros can be used in DML
What about DDL? For the most obvious DDL command as a use case – Create Table As Select – it works.
create or replace function get_filter(p_column_name in varchar2)
return varchar2 sql_macro(scalar) as
begin
return 'p_column_name';
end;
/
SQL> create table emp2
2 as
3 select * from emp
4 where get_filter (deptno) <=20;
Table EMP2 created.
Listing 6: scalar macros work for CTAS
But unfortunately it didn’t work for the other use cases that came to mind, such as filtered partition/table maintenance operations or function based index creation:
SQL> alter table emp move online
2 including rows where get_filter (deptno) <=40;
Error report -
ORA-14468: invalid INCLUDING ROWS WHERE ... clause
14468. 00000 - "invalid INCLUDING ROWS WHERE ... clause"
*Document: Yes
*Cause: An invalid INCLUDING ROWS WHERE ... clause was specified.
The INCLUDING ROWS WHERE ... clause has the following restrictions:
- It can only refer to the target table of the DDL.
- It cannot have subselect queries.
- It cannot refer to the SYSDATE function.
- It cannot refer to the ROWNUM pseudo column or the ROWID column.
*Action: Rewrite the WHERE clause so that it does not have the restrictions
previously listed.
SQL> create index emp_fbi on emp (upper_macro(ename));
Error report -
ORA-64632: SQL macro is not supported with Functional Index
64632. 00000 - "SQL macro is not supported with %s"
*Cause: The SQL macro was not supported with this feature.
*Action: Do not use SQL macro with this feature.
Listing 7: scalar macros don’t work in DDL
Nesting of SQL macros
The nesting of the SQL macros is not allowed. At least in terms of SQL statement. If we had defined two macros to lower the input and to add spaces after each character (don’t ask me why I’m using macros for this – it’s just to show, while keeping it simple), combining them in as single SQL statement wouldn’t work.
SQL> create or replace function lower_macro (p_param in varchar2)
2 return varchar2 sql_macro(scalar) as
3 begin
4 return 'lower(p_param)';
5 end;
6 /
Function LOWER_MACRO compiled
SQL>
SQL> create or replace function add_blanks_macro (p_param in varchar2)
2 return varchar2 sql_macro(scalar) as
3 begin
4 return q'[regexp_replace(p_param,'(.)','\1 ')]';
5 end;
6 /
Function ADD_BLANKS_MACRO compiled
SQL>
SQL> select add_blanks_macro(lower_macro(ename)) from emp;
Error starting at line : 23 in command -
select add_blanks_macro(lower_macro(ename)) from emp
Error at Command Line : 23 Column : 1
Error report -
SQL Error: ORA-64630: unsupported use of SQL macro: use of nested scalar SQL macro is not supported
Listing 8: nesting of scalar SQL macros is not supported
Instead, calling a SQL macro in the return string from another one will work. I haven’t found, or perhaps overlooked, where this was explicitly documented, but this type of nesting can be found in the examples in the documentation. Furthermore, the presence of this error ORA-64627: cycle detected in SQL macro expansion, tells me that it is intended to nest macros this way.
SQL> create or replace function add_blanks_lower_macro (p_param in varchar2)
2 return varchar2 sql_macro(scalar) as
3 begin
4 return q'[regexp_replace(lower_macro(p_param),'(.)','\1 ')]';
5 end;
6 /
Function ADD_BLANKS_LOWER_MACRO compiled
SQL> select add_blanks_lower_macro(ename) ename from emp;
ENAME
------------
s m i t h
a l l e n
w a r d
j o n e s
m a r t i n
b l a k e
c l a r k
...
Listing 9: but nesting in the macro body is supported
Of course, this limits you in terms of interoperability of your SQL macros. You cannot develop a lot of small modular macros and then use them how you need including nesting. Instead, you have to plan for useful combinations upfront and create separate dedicated macros for these combinations (consider the name add_blanks_lower_macro telling about two defined operations). Nevertheless, if you identified the useful combination, you don’t have to duplicate code inside its implementation. Hmm… OK, well… sort of 😉 You may ask, why not just call both modular macros in the newly defined add_blanks_lower_macro, instead of re-implementing the REGEXP_REPLACE in it? Because you cannot! Just single nesting at once is allowed. Following this rule, the SQL engine will never see two nested macros, but instead an a SQL expression after first expansion which can contain a SQL macro call, which in turn will be expanded and can contain a macro call and so on. Consider second example in Listing 10.
SQL> create or replace function add_blanks_lower_slim_macro (p_param in varchar2)
2 return varchar2 sql_macro(scalar) as
3 begin
4 return 'add_blanks_macro(lower_macro(p_param))';
5 end;
6 /
Function ADD_BLANKS_LOWER_SLIM_MACRO compiled
SQL> select add_blanks_lower_slim_macro(ename) ename from emp;
Error starting at line : 48 in command -
select add_blanks_lower_slim_macro(ename) ename from emp
Error at Command Line : 48 Column : 1
Error report -
SQL Error: ORA-64630: unsupported use of SQL macro: use of nested scalar SQL macro is not supported
64630. 00000 - "unsupported use of SQL macro: %s"
*Cause:
*Action:
SQL>
SQL>
SQL> create or replace function upper_add_blanks_lower_macro (p_param in varchar2)
2 return varchar2 sql_macro(scalar) as
3 begin
4 return 'upper(add_blanks_lower_macro(p_param))';
5 end;
6 /
Function UPPER_ADD_BLANKS_LOWER_MACRO compiled
SQL>
SQL> set echo on
SQL> column ename format a12
SQL> select upper_add_blanks_lower_macro(ename) ename from emp;
ENAME
------------
S M I T H
A L L E N
W A R D
J O N E S
M A R T I N
B L A K E
C L A R K
S C O T T
Listing 10: only single nesting at once
Nesting of table macros
What about table macros? You can nest them this way too. Consider Listing 11: we want to combine a table macro TOP_N which helps us to limit the rows in the result of a query with another macro that can the columns shown in the result set. Again, you cannot just implement to macros for these two tasks and then define the third macro that combines these two ones. Only single nesting at once is possible this way. So we implement the TOP_N macro as defined by its task and then we have to “sacrifice” another one and it will contain its own logic to filter out the columns as well as calling the nested TOP_N macro. We will call it EXCEPT_TOP_N respectively to indicate that it encapsulates two tasks at once.
SQL> create or replace function top_n (p_tab in dbms_tf.table_t
2 , p_limit in number
3 , p_order in dbms_tf.columns_t
4 )
5 return varchar2 sql_macro is
6 v_order_list varchar2(2000);
7 begin
8 -- turn PL/SQL table to comma separated list for ORDER BY clause
9 select listagg(replace(column_value,'"'),',') into v_order_list from table (p_order);
10 return 'SELECT * FROM p_tab ORDER BY '||v_order_list||
11 ' FETCH FIRST p_limit ROWS ONLY';
12 end;
13 /
Function TOP_N compiled
SQL> create or replace function except_top_n (p_tab in dbms_tf.table_t
2 , p_limit in number
3 , p_order in dbms_tf.columns_t
4 , p_except in dbms_tf.columns_t)
5 return varchar2 sql_macro is
6 v_order_list varchar2(2000);
7 v_select_list varchar2(2000);
8 all_cols dbms_tf.columns_t := dbms_tf.columns_t();
9 begin
10 for i in 1..p_tab.column.count loop
11 all_cols.extend;
12 all_cols(i):=p_tab.column(i).description.name;
13 end loop;
14 select listagg(replace(column_value,'"'),',')
15 into v_order_list
16 from table (p_order);
17
18 select listagg(replace(column_value,'"'),',')
19 into v_select_list
20 from (select * from table (all_cols)
21 minus
22 select * from table (p_except));
23 return 'SELECT '||v_select_list||' FROM top_n(p_tab, p_limit, columns('||v_order_list||'))';
24 end;
25 /
Function EXCEPT_TOP_N compiled
SQL> select * from except_top_n(emp, 10, columns (job, empno), columns (comm, sal));
EMPNO ENAME JOB MGR HIREDATE DEPTNO
---------- ------------ --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 1987-04-19 20
7902 FORD ANALYST 7566 1981-12-03 20
7369 SMITH CLERK 7902 1980-12-17 20
7876 ADAMS CLERK 7788 1987-05-23 20
7900 JAMES CLERK 7698 1981-12-03 30
7934 MILLER CLERK 7782 1982-01-23 10
7566 JONES MANAGER 7839 1981-04-02 20
7698 BLAKE MANAGER 7839 1981-05-01 30
7782 CLARK MANAGER 7839 1981-06-09 10
7839 KING PRESIDENT 1981-11-17 10
Listing 11: Nesting of table SQL macros
As you can see, passing the parameters become a little bit trickier this time. The scalar and table parameters are passed seamlessly but this doesn’t work with column parameters. No substitution takes place for them when leaving the SQL macro. That’s why we cannot just put the parameter in the return string like we could do for scalar and table parameters. Instead, we have to take the columns parameter in the macro body – which is available for us as a table DBMS_TF.COLUMNS_T – and process it as we need to generate a column list as a string and then concatenate it with the result string. This means we have to construct a COLUMNS parameter in this way again if we have to pass it to the nested SQL macro. Look at the Listing 11 again: the lines 30-32 are actually not needed to accomplish the task of the second macro, they are just there to be able to construct the COLUMNS parameter to pass it into the TOP_N macro in the line 39.
Conclusion
There are actually more possibilities to call SQL macros as explicitly mentioned in the documentation. It is a matter of having a good use case to use them on this unusual places. It is a pity that it is not easily possible to define a set of fine-granular SQL macros encapsulating just one task and then combine them as desired. The nesting in the body of a macro is possible but often requires more effort and up-front planning.