SQL MACROs + WITH Clauses = Prohibited!!

/ paulzipblog

The addition of SQL Macros in Oracle 21c (and some aspects back ported to 19c versions) were an excellent addition to DB developer’s tool sets. They allow us to factor out common SQL expressions and statements into reusable, parameterized constructs that can be used in other SQL statements. Giving capabilities like parameterized views or better refactoring and reuse for things like complex SQL related blocks. However, there is, in my opinion, a massive restriction associated with them, which cripples their usability in modern query writing and that’s when they are utilised in conjunction with a WITH clause…

Here are some really simple examples of the problem. Note, these examples are kept as simple as possible for comprehension and demonstration purposes. 

-- 1. SQL Macro (TABLE) - Non WITH based definition
create or replace function SQLMacroTable(pValue integer) return varchar2 sql_macro is
begin
  return 'select SQLMacroTable.pValue as value from dual';
end;
/

Function created.


-- 1. a) Used in simple query
select *
from SQLMacroTable(1)
/

     VALUE
----------
         1


-- 1. b) Used in WITH clause
with data as (
  select *
  from SQLMacroTable(1)
)
select * from data
/
with data as (
*
ERROR at line 1:
ORA-64630: unsupported use of SQL macro: use of SQL macro inside WITH clause is not supported



-- 1. c) Used in inline view
select *
from (
  select *
  from SQLMacroTable(1)
)
/

     VALUE
----------
         1


--------------------------------------------------------------------------------

-- 2. SQL Macro (TABLE) - WITH based definition
create or replace function SQLMacroTable(pValue integer) return varchar2 sql_macro is
begin
  return 'with data as (select SQLMacroTable.pValue as value from dual) select * from data';
end;
/

Function created.


-- 2. a) Used in simple query
select *
from SQLMacroTable(1)
/
from SQLMacroTable(1)
                    *
ERROR at line 2:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'SQLMACROTABLE'



-- 2. b) Used in WITH clause
with data as (
  select *
  from SQLMacroTable(1)
)
select * from data
/
with data as (
*
ERROR at line 1:
ORA-64630: unsupported use of SQL macro: use of SQL macro inside WITH clause is not supported



-- 2. c) Used in inline view
select *
from (
  select *
  from SQLMacroTable(1)
)
/
)
*
ERROR at line 5:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'SQLMACROTABLE'



--------------------------------------------------------------------------------

-- 3. SQL Macro (SCALAR) definition
create or replace function SQLMacroScalar(pValue integer) return varchar2 sql_macro(scalar) is
begin
  return 'SQLMacroScalar.pValue * 2';
end;
/

Function created.


-- 3. a) Used in simple query
select SQLMacroScalar(2) sms
from dual
/

       SMS
----------
         4


-- 3. b) Used in WITH clause
with data as (
  select SQLMacroScalar(2) sms
  from dual
)
select * from data
/
with data as (
*
ERROR at line 1:
ORA-64630: unsupported use of SQL macro: use of SQL macro inside WITH clause is not supported



-- 3. c) Used in inline view
select *
from (
  select SQLMacroScalar(2) sms
  from dual
)
/

       SMS
----------
         4

As you can see, you currently (Oracle 21.3c, November 2022) cannot use SQL Macros (table or scalar) among a WITH clause. This is both when defining a SQL Macro using a WITH clause or when using a call to a SQL Macro in a WITH clause. Which, given how ubiquitously WITH clauses are used in modern DB and SQL development, and given that they themselves were introduced to give better refactoring of queries – defeats the point. Although you can use SQL Macros in inline views and subqueries as a workaround, in my humble opinion, this is a massive oversight and restriction to a very useful piece of functionality.

5 thoughts on “SQL MACROs + WITH Clauses = Prohibited!!”

  1. Hi PaulZip,

    We can work around the bug you identified in case 2a) by using a DBMS_TF.TABLE_T input parameter and putting all the bind variables in a WITH clause. Example:

    SQL> create or replace function SQLMacroTable(pTable dbms_tf.table_t)
  2    return varchar2 sql_macro is
  3  begin
  4    return '
  5      with data1 as (
  6        select * from pTable where n in (select n from pTable)
  7      )
  8      select * from data1 
  9    ';
 10  end;
 11  /

Function SQLMACROTABLE compiled

SQL> with data(n) as (select 1 from dual)
  2  select * from SQLMacroTable(data);

         N
----------
         1

    This works with bind variables and bind peeking. I haven’t tested adaptive cursor sharing.

    Best regards,
    Stew Ashton

    Liked by 1 person

    Reply

  2. There is another workaround tried for case 2a – defining a variable within macro function and use it instead of parameter:

    create or replace function SQLMacroTable(pValue integer) return varchar2 sql_macro is
    v_value integer := pValue;
    begin
    return ‘with data as (select ‘||v_value||’ as value from dual) select * from data’;
    end;
    /

    select * from SQLMacroTable(1); works in that case

    Like

    Reply

    • Thanks for your contribution. That may work with numeric inputs, but not string based inputs. 

      create or replace function SQLMacroTable(pValue varchar2) return varchar2 sql_macro is
v_value varchar2(100) := pValue;
begin
  return 'with data as (select '''||v_value||''' as value from dual) select * from data';
end;
/

select * from SQLMacroTableStr('hello');

VALUE
--------
{NULL}

      Also, that approach will likely cause an excessive number of hard parses.

      Like

      Reply

Leave a comment