When can I use SQL Macros?

September 14, 2020

1–2 minutes

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! Smile

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

16 responses to “When can I use SQL Macros?”

lh

November 3, 2020 at 3:47 am

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?

Reply
1. Connor McDonald
  
  November 6, 2020 at 2:23 pm
  
  Would need to see a test case
  
  Reply
Randolf Eberle-Geist

November 3, 2020 at 7:04 pm

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

Reply
1. Connor McDonald
  
  November 6, 2020 at 3:10 pm
  
  Checking internally and will get back to you
  
  (and I’ve reproduced in my 19c)
  
  Reply
2. Connor McDonald
  
  November 6, 2020 at 6:21 pm
  
  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>
  
  Reply
LH

November 11, 2020 at 8:17 pm

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

Reply
1. Connor McDonald
  
  November 13, 2020 at 12:49 pm
  
  I’ll do some research and get back to you
  
  Reply
  1. lh
    
    November 23, 2020 at 6:00 pm
    
    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
    
    Reply
    1. Connor McDonald
      
      December 30, 2020 at 11:18 am
      
      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
      
      Reply
      1. ms
        
        March 30, 2022 at 1:54 am
        
        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
      2. Connor McDonald
        
        March 30, 2022 at 8:13 am
        
        can you give me a full test case to explore
SQL Macros = SQL Injection ? – Learning is not a spectator sport

November 13, 2020 at 12:52 pm

[…] my recent post about SQL Macros and their availability in 19c, I had some comments about parameter passing, these […]

Reply
"Big Mac and Size"..Handling large SQL Macros – Learning is not a spectator sport

January 4, 2021 at 6:57 pm

[…] because official I’m still on Christmas holidays. I had a question come in regarding the cool SQL Macro features in 19c and beyond. A SQL Macro generates SQL or a SQL fragment as an output from the […]

Reply
lh

January 5, 2021 at 10:48 pm

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

Reply
Jim Dickson

July 19, 2021 at 8:07 pm

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.

Reply
1. Connor McDonald
  
  July 21, 2021 at 10:04 am
  
  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
  
  Reply