Team,

the below demo was from Oracle Database 21c (21.3), can you help us to understand why the macro substitution was not observed during dbms_utility.expand_sql_text invocation, but still available in 10053 Trace.

demo@PDB1> create or replace function foo(p_sal in number )
  2  return varchar2
  3  sql_macro(scalar) as
  4  begin
  5     return ' p_sal * 12 ';
  6  end;
  7  /

Function created.

demo@PDB1> variable x clob
demo@PDB1> exec dbms_utility.expand_sql_text(' select empno,sal,deptno,hiredate,foo(sal) as annual_sal from emp ',:x );

PL/SQL procedure successfully completed.

demo@PDB1> print x

X
--------------------------------------------------------------------------------
SELECT "A1"."EMPNO" "EMPNO","A1"."SAL" "SAL","A1"."DEPTNO" "DEPTNO","A1"."HIREDA
TE" "HIREDATE","DEMO"."FOO"("A1"."SAL") "ANNUAL_SAL" FROM "DEMO"."EMP" "A1"


demo@PDB1> @tkfilename
C:\ORACLE\ORA21C\diag\rdbms\orcl\orcl\trace\orcl_ora_18616.trc
demo@PDB1> @10053

Session altered.

demo@PDB1> explain plan for
  2  select empno,sal,
  3      deptno,hiredate,
  4      foo(sal) as annual_sal
  5  from emp ;

Explained.

demo@PDB1> exit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

and the Final query after transformations from 10053 shows this

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "EMP"."EMPNO" "EMPNO",
 "EMP"."SAL" "SAL",
 "EMP"."DEPTNO" "DEPTNO",
 "EMP"."HIREDATE" "HIREDATE",
 "EMP"."SAL"*12 "ANNUAL_SAL" 
FROM "DEMO"."EMP" "EMP"

Team,

The below testcase was modelled like our application query and got many WITH clause query blocks.
when we apply the filter using the input parameter (p_date) within the WITH clause query block it doesn't work.
but works when applied in the MAIN from clause.

so what would be the workaround for having the input parameter within the WITH clause query block?
should i do string concatenation? if so then i would be losing the benefits of bind variables.

rajesh@ATP19C> create or replace function foo(p_date in date default null)
  2  return varchar2
  3  sql_macro as
  4  begin
  5     return ' with r1 as ( select * from t1 where created > p_date )
  6             select *
  7             from r1 ';
  8  end;
  9  /

Function created.

rajesh@ATP19C> select count(*) from foo( sysdate-10 );
select count(*) from foo( sysdate-10 )
                                     *
ERROR at line 1:
ORA-00904: "P_DATE": invalid identifier


rajesh@ATP19C> create or replace function foo(p_date in date default null)
  2  return varchar2
  3  sql_macro as
  4  begin
  5     return ' with r1 as ( select * from t1 )
  6             select *
  7             from r1
  8             where created > p_date ';
  9  end;
 10  /

Function created.

rajesh@ATP19C> select count(*) from foo( sysdate-10 );

  COUNT(*)
----------
       429

rajesh@ATP19C>

create or replace function foo(p_date in date default null)
return varchar2
sql_macro as
begin
   return 'select *
           from ( select * from t1 where created > p_date ) r1 ';
end;
/

create or replace function filter_foo(p_date in date default null)
return varchar2
sql_macro as
begin
   return 'select * from t1 where created > p_date';
end;
/

create or replace function foo(p_date in date default null)
return varchar2
sql_macro as
begin
   return 'select *
           from filter_foo ( p_date ) r1 ';
end;
/

The text returned by the SQL macro can assume a preceding WITH clause that contains the "parameter" values needed, so the macro itself does not have any parameters.

SQL> create table t1 as
  2  select trunc(sysdate) - level created from dual
  3  connect by level <= 9;

Table T1 created.

SQL> create or replace function foo
  2  return varchar2 sql_macro as
  3  begin
  4    return '
  5      with r1 as ( select t1.* from t1, parms where created > parms.p_date )
  6      select * from r1
  7    ';
  8  end;
  9  /

Function FOO compiled

SQL> with parms(p_date) as (select trunc(sysdate) - 3 from dual)
  2  select * from foo();

CREATED            
-------------------
2022-12-04 00:00:00
2022-12-03 00:00:00

Stew,
You scare me with those SQLs... LOL
Every time I see a clever use of sql capabilities like this, it feels like I am few decades away from learning Oracle.

Take a bow!!!

I am using Oracle 19c as well. There seems to be another bug present:

with
function f(x number)
return clob sql_macro as begin return q''
    select f.x
''; end;
select * from f(42);

Results in:
ORA-00600: internal error code, arguments: [kxes_sqlcomp_check_macro-1], [], [], [], [], [], [], [], [], [], [], []
00600. 00000 - "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause: This is the generic internal error number for Oracle program
exceptions. It indicates that a process has encountered a low-level,
unexpected condition. The first argument is the internal message
number. This argument and the database version number are critical in
identifying the root cause and the potential impact to your system.

Returning a clob (without the sql_macro) works just fine

with
function f(x number)
return clob as begin return q''
    select ''||f.x; 
end;
select f(42) from dual;

This seems to be a bug, I'd guess?
Does this bug still persist in Oracle versions beyond 19c?

select banner from v$version;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

with function f(x number)
return clob sql_macro as begin return q''
    select f.x
''; end;
select * from f(42);
/

         X
----------
        42

Good to know. Thank you!

Is a hotfix for 19c within reach, or should I not keep my hopes up?

Team,

the below test case constructed like our application demo.

added "dbms_output" calls to macros to show the constructed sql's
when the macro get executed as part of dml's - it errors.

can you help us to understand why it is? and any possible work around for us?

demo@PDB1> create table t1( c1 int, x number, y varchar2(10) );

Table created.

demo@PDB1> insert into t1 values( 1,7499,'ENAME' );

1 row created.

demo@PDB1> insert into t1 values( 1,7521,'ENAME' );

1 row created.

demo@PDB1>
demo@PDB1> create or replace function foo( p_input1 in number,p_input2 in number )
  2  return clob
  3  sql_macro
  4  as
  5     l_sql long :=' select empno,';
  6  begin
  7
  8     for x in ( select distinct y from t1 where c1 = p_input1 )
  9     loop
 10             l_sql := l_sql ||'lower('|| x.y ||') as x2 ,';
 11     end loop;
 12     l_sql := trim(',' from l_sql);
 13     l_sql := l_sql || ' from emp where deptno = p_input2 ';
 14
 15     dbms_output.put_line(' l_sql ==> '|| l_sql );
 16     return l_sql ;
 17  end;
 18  /

Function created.

demo@PDB1> select * from foo(1,10);

     EMPNO X2
---------- ----------
      7782 clark
      7839 king
      7934 miller

 l_sql ==>  select empno,lower(ENAME) as x2  from emp where deptno = p_input2
demo@PDB1> create or replace package pkg
  2  as
  3     procedure p1( p_input in number );
  4  end;
  5  /

Package created.

demo@PDB1> create or replace package body pkg
  2  as
  3     procedure p1( p_input in number ) as
  4     begin
  5             merge into emp e1 using (
  6                     select empno,x2
  7                             from foo(p_input,p_input*10) ) e2
  8             on ( e1.empno = e2.empno )
  9             when matched then
 10                     update set e1.ename = e2.x2;
 11
 12             dbms_output.put_line( ' sql%rowcount = '|| sql%rowcount );
 13     end;
 14  end;
 15  /
 l_sql ==>  select empno from emp where deptno = p_input2

Warning: Package Body created with compilation errors.

demo@PDB1> show err
Errors for PACKAGE BODY PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PL/SQL: SQL Statement ignored
6/17     PL/SQL: ORA-00904: "X2": invalid identifier
demo@PDB1>
demo@PDB1> create or replace package body pkg
  2  as
  3     procedure p1( p_input in number )
  4     as
  5             l_sql long;
  6     begin
  7             l_sql := q'# merge into emp e1 using (
  8                     select empno,x2 from foo( :p_input, :p_input*10) ) e2
  9             on ( e1.empno = e2.empno )
 10             when matched then
 11                     update set e1.ename = e2.x2 #';
 12
 13             execute immediate l_sql using p_input,p_input ;
 14             dbms_output.put_line( ' sql%rowcount = '|| sql%rowcount );
 15     end;
 16  end;
 17  /

Package body created.

demo@PDB1> select empno,ename from emp where deptno = 10;

     EMPNO ENAME
---------- ----------
      7782 CLARK
      7839 KING
      7934 MILLER

demo@PDB1> exec pkg.p1(7499);
 l_sql ==>  select empno from emp where deptno = p_input2
BEGIN pkg.p1(7499); END;

*
ERROR at line 1:
ORA-00904: "X2": invalid identifier
ORA-06512: at "DEMO.PKG", line 13
ORA-06512: at line 1


demo@PDB1>

create or replace function f ( p int ) 
  return clob sql_macro as
begin
  dbms_output.put_line ( 'p = ' || p );
  if p = 1 then 
    return q'! select 'equals 1' from dual !';
  elsif p is null then
    return q'! select 'is null' from dual !';
  end if;
end f;
/
var v number;
exec :v := 1;
select * from f ( :v );

'ISNULL
-------
is null

p = 

create table t (
  c1 int
);

insert into t values ( 1 );

create or replace function f 
  return clob sql_macro as
  max_val number;
begin
  select max ( c1 ) into max_val
  from   t;
  
  return ' select ' || max_val || ' v from dual ';
  
end f;
/

select * from f();
/*
         V
----------
         1
*/

insert into t values ( 2 );

select max ( c1 ) from t;
/*
   MAX(C1)
----------
         2
*/

select * from f();
/*
         V
----------
         1
*/

Hard code the parameter value in returned sql. Sure it is ugly and floods the shared pool like a noob gui programmer, but if the default is most common case and hard coded values tend to center around the current date, it might be acceptable:

create or replace function foo(p_date in date default null)
return varchar2
sql_macro as
begin
return 'with r1 as ( select * from t1 '
||CASE WHEN p_date IS NOT NULL THEN
q'{where created > TO_DATE('}'||TO_CHAR(p_date)||q'{')}'
END
||') select *
from r1 ';
end;
/

var stmt clob;
set long 100000

exec dbms_utility.expand_sql_text('select * from foo ( sysdate ) ',:stmt );
print :stmt;
/*
select
  "A1"."CREATED" "CREATED"
from
  (
    select
      "A3"."CREATED" "CREATED"
    from
      (
        select
          "A4"."CREATED" "CREATED"
        from
          (
            select
              "A2"."CREATED" "CREATED"
            from
              "CHRIS"."T1" "A2"
          ) "A4"
      ) "A3"
  ) "A1"
*/

exec dbms_utility.expand_sql_text(q'! select * from foo ( to_date ( :dt, 'yyyy-mm-dd' ) ) !',:stmt );
print :stmt;
/*
select
  "A1"."CREATED" "CREATED"
from
  (
    select
      "A3"."CREATED" "CREATED"
    from
      (
        select
          "A4"."CREATED" "CREATED"
        from
          (
            select
              "A2"."CREATED" "CREATED"
            from
              "CHRIS"."T1" "A2"
          ) "A4"
      ) "A3"
  ) "A1"
*/

exec dbms_utility.expand_sql_text(q'! select * from foo ( date'2023-11-01' ) !', :stmt );
print :stmt;
/*
select
  "A1"."CREATED" "CREATED"
from
  (
    select
      "A3"."CREATED" "CREATED"
    from
      (
        select
          "A4"."CREATED" "CREATED"
        from
          (
            select
              "A2"."CREATED" "CREATED"
            from
              "CHRIS"."T1" "A2"
            where
              "A2"."CREATED" > to_date ('01-NOV-2023 00:00')
          ) "A4"
      ) "A3"
  ) "A1"
*/