Oracle: Table macros

The value passed to a table macro can appear in the returned select statement anywhere where also a bind variable might be placed.

create or replace function tq84_table_macro(p varchar2) return varchar2 sql_macro(table) is begin return q'{ select object_name, p from user_objects where object_name like upper('%' || p || '%') }'; end tq84_table_macro; / select * from tq84_table_macro('du');

Parameter is NULL outside of the returned SQL text

Outside of the returned SQL text, the parameter(s) is null.

create or replace function tq84_table_macro(p varchar2) return varchar2 sql_macro(table)
is
   col_alias varchar2(128);
begin

   col_alias := nvl(p, 'p_is_null');
   
   return '

       select
          ''returned value'' as ' || col_alias || '
       from
          dual';

end tq84_table_macro;
/

select * from tq84_table_macro('non_null_value');
-- P_IS_NULL     
-- --------------
-- returned value

A consequence of this is that it is not possible to return a fundamentally different SQL text depending on the value passed to the function when it was invoked.

Oracle: Table macros

Parameter is NULL outside of the returned SQL text

See also