SQL macros and WITH clause are not known to be great friends: you cannot call a SQL macro in a WITH clause and if you want to define a table macro returning a query containing a WITH subquery(ies), then you won’t be able to use scalar parameters in this subquery. In the previous post I tried to explain another restriction from the technical perspective looking at the SQL macro expansion trace information. In this post I will try to understand the WITH clause related limitations using the same approach.
Let’s first create a simple table SQL macro and show how to obtain SQM expansion trace for it. I’m using a pipelined function sqm_trace_info introduced in the previous post for this.
SQL> create or replace function my_emp(p_ename in varchar2)
2 return varchar2 sql_macro is
3 begin
4 return 'select * from emp where ename = p_ename';
5 end;
6 /
Function MY_EMP compiled
SQL>
SQL> select * from sqm_trace_info(q'[select * from my_emp('KING')]');
COLUMN_VALUE
---------------------------------------------------------------
qksptfSQM_GetTxt(): Anonymous Block
===================================
declare
begin
:macro_text := "MY_EMP"(:3 );
end;
qksptfSQM_GetTxt(): Macro Text
==============================
select * from emp where ename = p_ename
qksptfSQM_Template(): Template Text
===================================
select "SYS__$".*
from (select NULL "P_ENAME" from SYS.DUAL) "MY_EMP",
lateral(select * from emp where ename = p_ename) "SYS__$"
SQL> select empno from my_emp('KING');
EMPNO
----------
7839
Listing 1: Simple table SQM with a scalar parameter
Note that it was not mandatory to prefix the parameter name with SQL macro function name in the result string. The lines 32-34 show the template query. All scalar parameters are gathered in one subquery selecting from dual. This subquery has the same name as our SQM function. The subquery in the LATERAL clause is actually what we return from SQM.
Now let’s change the SQM and introduce a WITH clause in the returned query, so that a scalar parameter is referenced inside this subquery.
SQL> create or replace function my_emp_with(p_ename in varchar2)
2 return varchar2 sql_macro is
3 begin
4 return 'with data as (select * from emp where ename = p_ename)
5 select * from data';
6 end;
7 /
Function MY_EMP_WITH compiled
SQL>
SQL>
SQL> select * from sqm_trace_info(q'[select * from my_emp_with('KING')]');
Error starting at line : 10 in command -
select * from sqm_trace_info(q'[select * from my_emp_with('KING')]')
Error at Command Line : 10 Column : 15
Error report -
SQL Error: ORA-00904: "P_ENAME": invalid identifier
ORA-06512: at "ONFTEST.SQM_TRACE_INFO", line 14
00904. 00000 - "%s: invalid identifier"
Listing 2: Putting scalar parameter in a WITH clause fails
The parsing fails with ORA-00904. We cannot get SQM expansion trace output in this case. But we can imagine how the template query could look like looking at Listing 1. Just substitute the query in the LATERAL clause with the actual one. Nothing else has changed.
SQL> select "SYS__$".*
2 from (select NULL "P_ENAME" from SYS.DUAL) "MY_EMP_WITH",
3 lateral(with data as (select * from emp where ename = p_ename)
4 select * from data) "SYS__$";
Error starting at line : 1 in command -
select "SYS__$".*
from (select NULL "P_ENAME" from SYS.DUAL) "MY_EMP_WITH",
lateral(with data as (select * from emp where ename = p_ename)
select * from data) "SYS__$"
Error at Command Line : 3 Column : 60
Error report -
SQL Error: ORA-00904: "P_ENAME": invalid identifier
00904. 00000 - "%s: invalid identifier"
Listing 3: Inspect the assumed template query
Now, we can see why it doesn’t work. It is just the question of the scope in SQL and exposed (implicit and explicit) table names. The column P_ENAME coming from the subquery “MY_EMP_WITH” can be referenced in the main query of the LATERAL clause (select * from data), but it is not reachable from its WITH clause over two levels of nesting.
Quite odd, if we prefix the scalar parameter with the function name, we will observe another error.
SQL> create or replace function my_emp_with(p_ename in varchar2)
2 return varchar2 sql_macro is
3 begin
4 return 'with data as (select * from emp where ename = my_emp_with.p_ename)
5 select * from data';
6 end;
7 /
Function MY_EMP_WITH compiled
SQL>
SQL> select * from sqm_trace_info(q'[select * from my_emp_with('KING')]');
Error starting at line : 9 in command -
select * from sqm_trace_info(q'[select * from my_emp_with('KING')]')
Error at Command Line : 9 Column : 15
Error report -
SQL Error: ORA-06553: PLS-306: wrong number or types of arguments in call to 'MY_EMP_WITH'
Listing 4. With prefixed scalar parameters we got ORA-06553
This is because the parser cannot see the subquery named as ‘MY_EMP_WITH’ out of the WITH clause. But it is aware of the existing function with this name (our SQL macro). And it tries to interpret the reference as a call of it.
Once again, it is allowed to define a table SQM that returns a query containing a WITH clause. However, this WITH subquery cannot contain any scalar parameters.
By the way, there is a workaround suggested by Stew Ashton using a table parameter DBMS_TF.TABLE_T, you can find it here.
No macros inside a WITH clause
In contrast to the upper restriction, the next one is documented, at least it has a dedicated error message: ORA-64630: unsupported use of SQL macro: use of SQL macro inside WITH clause is not supported
It is not easy to understand why it shouldn’t be possible. At least for table macros, I think it is a consequence of the fact, that you are allowed to return a query with a WITH clause as a result string. The final query would have a nested WITH clause and end up with ORA-32034: unsupported use of WITH clause. In case you don’t return a WITH clause from your SQM, it works just fine.
Another reason could be that using table parameters leads to a template query like in Listing 5. Every table parameter becomes a WITH subquery.
create or replace function sqm_tab_noop (t dbms_tf.table_t) return varchar2 sql_macro
is
begin
return 'select * from t';
end;
/
select * from sqm_trace_info('select * from sqm_tab_noop (dual)');
...
qksptfSQM_Template(): Template Text
===================================
with T as (select /*+ INLINE */ * from "SYS"."DUAL")
select "SYS__$".*
from (select * from t) "SYS__$" );
Listing 5: Using table parameters
So, if there is a need to combine this templating query with the main query which is calling this macro, then again, we will have two nested WITH clauses.
In my eyes, not being able to call a SQL macro in the WITH subquery, not even a scalar SQM, is a really critical limitation. WITH clauses (CTE) are really a very good way of making complex SQL more readable. This is also one of the primary goals of the SQL macros. Unfortunately, they prevent each other in this case. I really hope that this restriction will soon be removed.