Oracle SQL macros: basic example

First, we need some test data:

drop table someData purge;

create table someData (
   id    varchar2(5)   primary key,
   num   number,
   txt   varchar2(20)
);

insert into someData values ('foo', 42, 'twenty-four');
insert into someData values ('bar', 99, 'ninety-nine');
insert into someData values ('baz', -2, 'minus two'  );

commit;

The following function returns the text of a select statement.

The values of the parameters of the function (here: p_id), will be replaced by bind variables in the returned SQL statement:

create or replace function selectData(p_id varchar2) return varchar2 sql_macro
is begin

   return q'{

       select
          num,
          txt
       from
          someData
       where
          id = p_id -- Note that p_id is embedded in a string!
   }';

end selectData;
/

Note: because the parameter is used for bind variables, it can only be used where bind variables are permitted. In particular, this means that the parameter cannot be used to select dynamically from different tables.

Because the function is declared with sql_macro clause, it allows to be selected from:

select *        from selectData('foo');
--        NUM TXT
-- ---------- --------------------
--         42 twenty-four

select num, txt from selectData('bar');
-- 
--        NUM TXT
-- ---------- --------------------
--         99 ninety-nine

select txt, num from selectData('baz');
-- 
-- TXT                         NUM
-- -------------------- ----------
-- minus two                    -2