SQL Macros – Some Less Obvious Facts (Part 1)

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 SCALAR macro cannot appear in FROM clause of a query table expression. It can appear wherever PL/SQL functions are allowed, for example in the select list, the WHERE clause, and the ORDER BY clause.

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

Figure 1: Syntax diagrams for the FROM clause of a query

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.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *