These examples try to outline some pecularities when using function parameters with Oracle's SQL macro functions.
Demonstration tables
First, we need three tables, named T, T0 and T1. Each table contains a text that identifies the table name:
create table T (txt varchar2(10));
create table T0 (txt varchar2(10));
create table T1 (txt varchar2(10));
insert into T values ('This is T' );
insert into T0 values ('This is T0');
insert into T1 values ('This is T1');
The function randomTable returns a select statement that depending on the (alleged) current time selects from T0 or T1. The statement also adds compile_time and exec_time:
create or replace function randomTable return varchar2 sql_macro
is
compile_date varchar2(19) := to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
begin
return '
select
''' || compile_date || ''' compile_date,
to_char(sysdate, ''yyyy-mm-dd hh24:mi:ss'') exec_date,
txt
from
T' || mod(trunc(extract(second from systimestamp)), 2);
end randomTable;
/
If the SQL macro function randomTable is executed mutliple times, it turns out, that compile_time does not change and returns the time of when randomTable() was first called, however exec_time returns the time of the execution of the SQL statement.
The consequence of this behavior is also that the select statement does not change the table from which it selects, as naively expected.
Because randomTable() cannot be forced to change the select statement once it is defined, another approach is tried. The SQL macro function tabN has a parameter that is supposed to specify which table is selected from:
create or replace function tabN(n integer) return varchar2 sql_macro
is
begin
dbms_output.put_line('tabN being called, n = ' || n);
return 'select * from T' || mod(n, 2);
end tabN;
/
The behavior of the following select statement depends on the value of the initialization parameter cursor_sharing.
If this value is exact (which it should ideally be), the value of the parameter does determine the table from which the function selects.
However, if the parameter's value is force, the passed value is silently disregared (i.e. becomes null in the function), and the function selects from T.
The dbms_output.put_line() in the function can be used to show that the parameter n is always null, irrespective of the actual value passed to the function (if the init parameter is force).
tabN being called, n =
Using tabN in PL/SQL context
The whole story with the parameters of macro functions gets even more interesting when using such macro functions in a PL/SQL context, as for example shown below:
declare
procedure iterateWithVariable(n integer) is
begin
for r in (select txt from tabN(n)) loop
dbms_output.put_line(r.txt);
end loop;
end iterateWithVariable;
begin
for r in (select txt from tabN(1)) loop
dbms_output.put_line(r.txt);
end loop;
for r in (select txt from tabN(2)) loop
dbms_output.put_line(r.txt);
end loop;
iterateWithVariable(1);
iterateWithVariable(2);
end;
/
In this case. the for r in (select … from tabN(1) selects from from T1 and the for r in (select … from tabN(2) selects from T0.
But when passing the parameter via a function (iterateWithVariable), the function tabN selects from T again. This is because in this case, the parameter is passed as a bind variable and the function cannot know which value it has - so the value of the parameter n is passed as null, as already seen earlier.