Two types of SQL macros
There are two types of SQL macros:
- Table macros (available with 19c)
- Scalar macros (available with 21c)
Their main difference is that
- Table macros can only be used in the
from clause while
- Scalar macros can only be applied in the
select list or where clause of an SQL statement.
These types are declared with a sql_macro_clause as part of a create function statement:
function XYZ(…) return varchar2 sql_macro(table ) …
function XYZ(…) return varchar2 sql_macro(scalar) …
function XYZ(…) return varchar2 sql_macro …
Without explicitly stating sql_macro with either table or scalar, the function defaults to a table macro.
Definition and declaration in a package
When unsing SQL macros in a package, the keyword sql_macro appears in the package specification …
create or replace package tq84 as
function find (
pattern varchar2,
typ varchar2
) return varchar2 sql_macro;
end tq84;
/
… but not in the body:
create or replace package body tq84 as
…
function find (
pattern varchar2,
typ varchar2
) return varchar2 is
begin
return q'{
select *
from
syz v
where
v.typ = find.typ and
regexp_like(v.text, find.pattern, 'ni')
}';
end find;
end tq84;
/