ORA-64630: unsupported use of SQL macro: use of nested scalar SQL macro is not supported
A
scalar SQL macros cannot be used as part of the expression given to another SQL macro.
create or replace function tq84_macro(expr varchar2) return varchar2 sql_macro(scalar) is begin
return 'substr(expr, 2)';
end tq84_macro;
/
This SQL statement runs fine:
select
object_name,
tq84_macro(object_name) o2
from
user_objects;
This statement throws ORA-64630: unsupported use of SQL macro: use of nested scalar SQL macro is not supported:
select
object_name,
tq84_macro(tq84_macro(object_name)) o3
from
user_objects;
Calling a scalar macro from within another scalar macro, however, is possible:
create or replace function tq84_macro_wrap(expr varchar2) return varchar2 sql_macro(scalar) is begin
return 'tq84_macro(expr)';
end tq84_macro_wrap;
/
select
object_name,
tq84_macro_wrap(object_name) ow
from
user_objects;
drop function tq84_macro_wrap;
drop function tq84_macro;