Found the following in 19c (19.20), 23c FREE (23.3) and 23ai FREE (23.4).
Though the use of a macro inside a with clause is (currently) not possible, a table macro can return a query containing a with clause.
However, if within that with-clause I refer to a parameter, then at runtime an exception is raised:
“ORA-00904: "P_VALUE": invalid identifier”
When I move the reference of the parameter from the with clause to the main query it does work, so it appears to not be due to wrong coding.
In the script below two macros are created the first with a reference to the parameter from the main query, the other from the with clause.
Then both are used in a query.
As you can see in the screenshot of the run-result the first one works as expected, the second fails.
I don't think this is an intended limitation, because then I would have expected a specific ORA message.
It looks like at parse time the parameter placeholders are only substituted by their values if they are in the main select.
Script:
prompt Create macro with parameter in main select
create or replace function ero_macro_prm_in_main
(p_value in varchar2
)
return varchar2 sql_macro
is
l_sql varchar2(4000);
begin
l_sql := q'{with
get_string as
(
select 'Fixed Value' as fixed_value
from dual
)
select fixed_value
, p_value as param_value
from get_string
}';
return (l_sql);
end;
/
prompt Create macro with parameter in with clause
create or replace function ero_macro_prm_in_with
(p_value in varchar2
)
return varchar2 sql_macro
is
l_sql varchar2(4000);
begin
l_sql := q'{with
get_string as
(
select 'Fixed Value' as fixed_value
, p_value as param_value
from dual
)
select *
from get_string
}';
return (l_sql);
end;
/
prompt Query with macro with With clause and parameter in main select (=> WORKS)
select *
from ero_macro_prm_in_main('Input for Macro Param in Main Select')
;
prompt Query with macro with With clause and parameter in With clause (=> FAILS)
select *
from ero_macro_prm_in_with('Input for Macro Param in With Clause')
;
prompt Cleanup
drop function ero_macro_prm_in_main;
drop function ero_macro_prm_in_with;
Result:
