Posted by One of the very cool features we’ve been talking about for 20c is SQL Macros. But you no longer need to wait for a future release of the database to get access to all the goodness of SQL Macros.
Why? Because much of the functionality has now been backported to 19c, and is also now officially in the documentation so there’s no ambiguity as to whether you are supported to use them or not. They’ll be coming soon to an RU near you 🙂
There is also a MOS Note to assist with the data dictionary information about SQL macros.
If you are looking for a great primer on SQL Macros, check out Keith Laker’s blog post on them, and as you can see, they will be available in all permutations of the database on an Release Update of 19c. They are available on Autonomous Database and LiveSQL support is also there.
So…to answer the question of “When you can use SQL Macros?”… RIGHT NOW! 
And similarly, this is another reason to be moving to 19c, the long term support release.
Enjoy!
Addenda:
In LiveSQL and 19.8, where you would type:
SQL_MACRO(TABLE)
use
SQL_MACRO
to avoid compilation errors
Learning is not a spectator sport 
Hi
I could see great benefits in this e.g.
– in views where selection should be based on base table column which is not returned in view
– handling type mismatches (e.g. selection is based on date column and this should be used for two tables where timestamp and date columns are used)
– with analytical functions in views by having selection criteria executed before analytical function.
I tried this on simple production cases, and it worked fine. However when trying to use this in more complicated case I got errors: ORA-00904: “P_PAIVA”: invalid identifier. Just curious: Should this work with common table expressions?
Would need to see a test case
Hi Connor,
I’ve tried the following implementation of SQL Macros on 19.7 and 19.8:
https://blog.sqlora.com/en/sql-macros-in-oracle-19c/
The problem in 19c seems to be that any non-default scalar parameter to the SQL Macro is actually NULL inside the function (see comments section of the post linked to above).
Can you confirm this?
Is this different from the 20c preview behaviour (don’t have access to 20c at the moment)?
If yes, is this a known limitation / bug and will be fixed in the future?
Thanks,
Randolf
Checking internally and will get back to you
(and I’ve reproduced in my 19c)
Just to add…. numbers seem fine, and strings as well but only if referenced directly within the SQL string rather than an expression to build the SQL
SQL> create or replace
2 view empv as select empno, ename from scott.emp;
View created.
SQL>
SQL> create or replace
2 function add_col(p_input_data dbms_tf.table_t
3 ,p_literal number default 0
4 ) return clob sql_macro as
5 v_sql varchar2(1000);
6 begin
7 dbms_output.put_line(‘p_literal=’||p_literal);
8 v_sql := ‘select x.*,’||p_literal||’ col from get_csv.p_input_data x’;
9 return v_sql;
10 end;
11 /
Function created.
SQL>
SQL> set serverout on
SQL> select * from add_col(p_literal=>1,p_input_data=>empv);
EMPNO ENAME COL
———- ———- ———-
7369 SMITH 1
7499 ALLEN 1
7521 WARD 1
7566 JONES 1
7654 MARTIN 1
7698 BLAKE 1
7782 CLARK 1
7788 SCOTT 1
7839 KING 1
7844 TURNER 1
7876 ADAMS 1
7900 JAMES 1
7902 FORD 1
7934 MILLER 1
14 rows selected.
p_literal=1
SQL>
SQL> select * from add_col(p_literal=>2,p_input_data=>empv);
EMPNO ENAME COL
———- ———- ———-
7369 SMITH 2
7499 ALLEN 2
7521 WARD 2
7566 JONES 2
7654 MARTIN 2
7698 BLAKE 2
7782 CLARK 2
7788 SCOTT 2
7839 KING 2
7844 TURNER 2
7876 ADAMS 2
7900 JAMES 2
7902 FORD 2
7934 MILLER 2
14 rows selected.
p_literal=2
SQL>
SQL> create or replace
2 function add_col(p_input_data dbms_tf.table_t
3 ,p_literal varchar2 default ‘string’
4 ) return clob sql_macro as
5 v_sql varchar2(1000);
6 begin
7 dbms_output.put_line(‘p_literal=’||p_literal);
8 v_sql := ‘select x.*,p_literal col from get_csv.p_input_data x’;
9 return v_sql;
10 end;
11 /
Function created.
SQL>
SQL> select * from add_col(p_input_data=>empv);
EMPNO ENAME COL
———- ———- ——
7369 SMITH string
7499 ALLEN string
7521 WARD string
7566 JONES string
7654 MARTIN string
7698 BLAKE string
7782 CLARK string
7788 SCOTT string
7839 KING string
7844 TURNER string
7876 ADAMS string
7900 JAMES string
7902 FORD string
7934 MILLER string
14 rows selected.
p_literal=string
SQL>
SQL> select * from add_col(p_literal=>’hello’,p_input_data=>empv);
EMPNO ENAME COL
———- ———- —–
7369 SMITH hello
7499 ALLEN hello
7521 WARD hello
7566 JONES hello
7654 MARTIN hello
7698 BLAKE hello
7782 CLARK hello
7788 SCOTT hello
7839 KING hello
7844 TURNER hello
7876 ADAMS hello
7900 JAMES hello
7902 FORD hello
7934 MILLER hello
14 rows selected.
p_literal=
SQL>
Hi
Simplified example of using in where clause data that is not in returned by select statement.
This works fine when criteria is in ‘main’ select, but when selection should be done on common table expression, this does not succeed.
create table t_lhtest1(
date1 date,
n1 number(10));
insert into t_lhtest1 select to_date(‘20200101′,’yyyymmdd’) + level/(24*60), level from dual connect by level = to_date(‘20200101′,’yyyymmdd’)
group by
trunc(date1,’MM’)
)
select
*
from
basequery;
CREATE OR REPLACE function VP_LHEXAMPLE1(P_PAIVA date) return varchar2 SQL_MACRO is
begin
return q'{with basequery as (
select
trunc(date1,’MM’) date1,
count(*)
from
t_lhtest1
where
date1 >= P_PAIVA
group by
trunc(date1,’MM’)
)
select
*
from
basequery
}’;
end;
/
SELECT * FROM VP_LHEXAMPLE1(to_date(‘20200401′,’yyyymmdd’));
[Error] Execution (362: 60): ORA-00904: “P_PAIVA”: invalid identifier
CREATE OR REPLACE function VP_LHEXAMPLE2(P_PAIVA date) return varchar2 SQL_MACRO is
begin
return q'{
select
trunc(date1,’MM’) date1,
count(*)
from
t_lhtest1
where
date1 >= P_PAIVA
group by
trunc(date1,’MM’)
}’;
end;
/
SELECT * FROM VP_LHEXAMPLE2(to_date(‘20200401′,’yyyymmdd’));
1.4.2020 43200
1.5.2020 44640
1.6.2020 43200
1.7.2020 44640
1.8.2020 44640
…
What I am doing wrong on VP_LHEXAMPLE1 ?
lh
I’ll do some research and get back to you
Hi
While trying to convert a rather long sql-statement into using parametrized views, I ran into error:
PLS-00172: string literal too long
Cause: The string literal was longer than 32767 bytes.
Action: Use a string literal of at most 32767 bytes.
Is it truly so that parametrized view cannot be longer that 32767 bytes ?
lh
How about returning a clob ?
SQL> create or replace
2 function fff return clob sql_macro is
3 c clob := ‘select dummy from dual where 1=0 and 2=0 and 3=0 and 4=0’;
4 begin
5 for i in 1 .. 1000 loop
6 c := c || ‘ union all select dummy from dual where 1=0 and 2=0 and 3=0 and 4=0’;
7 end loop;
8 return c;
9 end;
10 /
Function created.
SQL> select * from fff();
no rows selected
After creating any table macro when I use the same in select from, I get the following error
ORA-22905: cannot access rows from a non-nested table item
22905. 00000 – “cannot access rows from a non-nested table item”
*Cause: attempt to access rows of an item whose type is not known at
parse time or that is not of a nested table type
*Action: use CAST to cast the item to a nested table type
Error at Line: 1 Column: 15
can you give me a full test case to explore
Hi
Thank You for Your answer.
I ran into this in trying to convert a view, which truly is long and containing several unions and long in clauses, to use parametrized view. (and I am not the original writer of this view). The reason behind this is that by using parameter I could overcome problems due to mismatched datatypes and some branches of union not returning the column to be used as criteria.
I simply used return q’ {placed the little modified view text here syntax.}’
Now this is returning
[Error] PLS-00172 (4: 7): PLS-00172: string literal too long
I probably can overcome this error by using clob variable and put into it this view text in pieces as You have in Your example.
However using of q'{ } is quite simple method. Is there any method to have in q{‘ ‘} longer string ?
As they say: one should learn 3 three new things each day. I still have 2 to go.
lh
In 19.10 on premise I see some variations on same issue as LH
create or replace function f (input_n in integer) return varchar2 sql_macro as
begin
return q'(
with
d as ( select 1 as n, ‘one’ as name from dual union all select 2, ‘two’ from dual ),
v as ( select n, name from d
union all
select n, name from d where f.input_n is not null — or input_n is not null
)
select * from v — where v.n = f.input_n
)’ ;
end f ;
/
select * from f(2) ;
In the branch of union all, I am trying to implement disjunction but cannot reference either f.input_n or input_n – even though I can do so in final select of CTE.
Error is either ORA-00904 or ORA-06553: PLS-306: wrong number or types of arguments in call to ‘F’.
Will try to log with Oracle Support but would be good to know if this is a known bug, or am I doing something wrong.
I don’t think we support WITH inside a SQL macro, because you get a giant mess if you then tried to use that within in WITH statement of its own. I *thought* I’d seen this restriction somewhere in a doc, but I can’t find it currently