Oracle Scratchpad

July 22, 2021

SQL Macro

Filed under: 19c,Oracle — Jonathan Lewis @ 10:18 am BST Jul 22,2021

A question came up recently on the Oracle Developer forum that tempted me into writing a short note about SQL Macro functions – a feature that was touted for 20c but which has been back-ported [ed: in part, just the “table macro” feature – see comment 1] to the more recent releases of 19c. Specifically I set up this demo using 19.11.0.0.

The OP supplied a script to prepare some data. I’ll postpone that to the end of this note and start with variations of the query that could be used against that data set. I’ll be looking at the original query, a variant of the query that uses a pipelined function, then a variant that uses an SQL Macro function.

The requirement starts with a query to turn a pair of dates into a date range – which can be done in many ways but the OP had used a recursive “with subquery” (CTE/common table expression).

with calendar ( start_date, end_date ) as (
        select date '2021-07-01', date '2021-07-30' from dual
        union all
        select start_date + 1, end_date
        from   calendar
        where  start_date + 1 <= end_date
)
select start_date as day
from   calendar
;

Getting on to the full requirement we can use this subquery as if it were a table (or inline view) and join it to any other tables where we want data from a date range, for example:

select
        e.employee_id, c.day
from
        employees e
inner join
        (
                with calendar ( start_date, end_date ) as (
                        select date '2021-07-01', date '2021-07-30' from dual
                        union all
                        select start_date + 1, end_date
                        from   calendar
                        where  start_date + 1 <= end_date
                )
                select start_date as day
                from   calendar
        ) c
partition by
        (e.employee_id)
on      (substr(e.work_days, trunc(c.day) - trunc(c.day, 'IW') + 1, 1) = 'Y')
where
        not exists (
                select  1
                from    holidays h
                where   c.day = h.holiday_date
        )
and     not exists(
                select  1
                from    timeoff t
                where   e.employee_id = t.employee_id
                and     t.timeoff_date = c.day
        )
order by
        e.employee_id,
        c.day

If we want a report for a different month we just have to supply a different pair of dates, and we can probably work out a way of making it easy for the end-users to supply those dates as parameters to a report.

The pipelined function

However, we may want to use the same little “recursive CTE” (or similar) pattern in many different reports, and ad hoc queries that users might want to write for themselves. To avoid wasting time on logic, or basic typing errors, is it possible to hide some of the complexity of the subquery structure. The answer is yes, and for a long time we could have used a “pipelined function” to do this – though we have to create a simple object type and an object table type to do so. For example:

create or replace type obj_date is object (day date);
/

create or replace type nt_date is table of obj_date;
/

create or replace function generate_dates_pipelined(
        p_from  in date,
        p_to    in date
)
return nt_date 
pipelined
is
begin
        for c1 in (
                with calendar (start_date, end_date ) as (
                        select trunc(p_from), trunc(p_to) from dual
                        union all
                        select start_date + 1, end_date
                        from   calendar
                        where  start_date + 1 <= end_date
                )
                select start_date as day
                from   calendar
        ) loop
                pipe row (obj_date(c1.day));
        end loop;

        return;

end generate_dates_pipelined;
/

I’ve started by creating an object type with a single attribute called day of type date, and an object table type of that object type. This means I can use the object type and the object table type to pass data between SQL and PL/SQL. Then I’ve created a pl/sql function that returns the object table type, but in a pipelined fashion using the pipe row() mechanism to supply the data one object at a time.

In my final SQL I can now use the table() operator to cast the result of the function call from an object table to a relational table, implicitly mapping the object attributes to their basic Oracle data types.

select
        e.employee_id, c.day
from
        employees e
inner join
        table(generate_dates_pipelined(date '2021-07-01', date '2021-07-30')) c
partition by
        (e.employee_id)
on      (substr(e.work_days, trunc(c.day) - trunc(c.day, 'IW') + 1, 1) = 'Y')
where
        not exists (
                select  1
                from    holidays h
                where   c.day = h.holiday_date
        )
and     not exists(
                select  1
                from    timeoff t
                where   e.employee_id = t.employee_id
                and     t.timeoff_date = c.day
        )
order by
        e.employee_id,
        c.day
;

I’ve replaced the 9 lines of the inline “with subquery” by a single line call:

        table(generate_dates_pipelined(date '2021-07-01', date '2021-07-30')) c

In fact the table() operator hasn’t been needed since some time in the 12c timeline, but it might be useful as a little reminder of what’s going on behind the scenes. It’s also a reminder that the data really will behave as if it’s coming from a relational table rather then a pl/sql loop.

Although this pipelined function approach can be very effective another member of the forum pointed out that behind the scenes it is based on a pl/sql loop walking through a cursor which, in this example, was doing row by row processing (though it could be changed to bulk collect with a limit to improve performance a little). We might want to look at options for doing things differently.

The SQL Macro function

In many programming languages a “macro” is a symbol that is used as a short-hand for a longer piece of code. Even in environments like your favourite shell environment you can usually set up shorthand for longer texts that you use frequently, for example:

alias otr="cd /u01/app/oracle/diag/rdbms/or19/or19/trace"

The Oracle equivalent is a PL/SQL function (declared as a “SQL_Macro” function) that you include in your SQL statement, and at run-time Oracle will execute the function and use the text it returns to modify your statement [ed: see comment 1: this expansion is done at hard parse time, not at run-time] . Here’s the macro strategy applied to the date range generation:

create or replace function generate_dates_macro(
        p_from  in date,
        p_to    in date
)
return varchar2
sql_macro
is
        v_sql varchar2(4000) := q'{
                with calendar (start_date, end_date ) as (
                        select
                                to_date('xxxx-xx-xx','yyyy-mm-dd'),
                                to_date('yyyy-yy-yy','yyyy-mm-dd')
                        from    dual
                        union all
                        select start_date + 1, end_date
                        from   calendar
                        where  start_date + 1 <= end_date
                )
                select start_date as day
                from   calendar
                }'
        ;

begin
        v_sql := replace(v_sql,'xxxx-xx-xx',to_char(p_from,'yyyy-mm-dd'));
        v_sql := replace(v_sql,'yyyy-yy-yy',to_char(p_to  ,'yyyy-mm-dd'));

--      dbms_output.put_line(v_sql);
        return v_sql;

end generate_dates_macro;
/

I’ve created a function, flagged as a sql_macro, that returns a varchar2. It has two input parameters which are declared as dates. The initial value of the variable v_sql looks very similar to the CTE I used in the original query except the two “dates” it uses are “xxxx-xx-xx” and “yyyy-yy-yy”, but in the body of the function I’ve replaced those with the text forms of the two incoming date parameters. There’s a call to dbms_output.put_line() that I’ve commented out that will show you that the final text returned by the function is:

                with calendar (start_date, end_date ) as (
                        select
                                to_date('2021-07-01','yyyy-mm-dd'),
                                to_date('2021-07-30','yyyy-mm-dd')
                        from    dual
                        union all
                        select start_date + 1, end_date
                        from   calendar

                 where  start_date + 1 <= end_date
                )
                select start_date as day
                from   calendar

So now we can rewrite the original statement as follows (with just a minor change from the pipelined version):

select
        e.employee_id, c.day
from
        employees e
inner join
        generate_dates_macro(date '2021-07-01', date '2021-07-30') c
partition by
        (e.employee_id)
on      (substr(e.work_days, trunc(c.day) - trunc(c.day, 'IW') + 1, 1) = 'Y')
where
        not exists (
                select  1
                from    holidays h
                where   c.day = h.holiday_date
        )
and     not exists(
                select  1
                from    timeoff t
                where   e.employee_id = t.employee_id
                and     t.timeoff_date = c.day
        )
order by
        e.employee_id,
        c.day
;

When we first call this statement (i.e. assuming a suitable child cursor does not yet exist) Oracle evaluates the function, slots the generated text in place, then optimises and executes the resulting text instead. Interestingly the text reported by a call to dbms_xplan.display_cursor() shows the original text even though the plan clearly includes references to the table(s) in the SQL macro – a search of the library cache also shows the original text, but reveals an anonymous pl/sql block calling the SQL Macro function (in a style reminiscent of the way that row-level security (RLS, FGAC, VPD) calls a security predicate function) that is invisibly folded into a query.

declare
begin 
        :macro_ text := "GENERATE_DATES_MACRO"(
                TO_DATE(' 2021-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'),
                TO_DATE(' 2021-07-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
        );
end;

Here’s the execution plan for the query using the SQL Macro:

--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name       | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |            |      1 |        |    41 (100)|     83 |00:00:00.01 |     130 |       |       |          |
|   1 |  SORT ORDER BY                                |            |      1 |      1 |    41   (5)|     83 |00:00:00.01 |     130 |  9216 |  9216 | 8192  (0)|
|*  2 |   FILTER                                      |            |      1 |        |            |     83 |00:00:00.01 |     130 |       |       |          |
|*  3 |    HASH JOIN ANTI                             |            |      1 |      1 |    39   (3)|     84 |00:00:00.01 |      46 |  1744K|  1744K| 1542K (0)|
|   4 |     NESTED LOOPS                              |            |      1 |      1 |    21   (0)|     88 |00:00:00.01 |      23 |       |       |          |
|   5 |      TABLE ACCESS FULL                        | EMPLOYEES  |      1 |      1 |    17   (0)|      4 |00:00:00.01 |      23 |       |       |          |
|*  6 |      VIEW                                     |            |      4 |      1 |     4   (0)|     88 |00:00:00.01 |       0 |       |       |          |
|   7 |       UNION ALL (RECURSIVE WITH) BREADTH FIRST|            |      4 |        |            |    120 |00:00:00.01 |       0 |  2048 |  2048 | 2048  (0)|
|   8 |        FAST DUAL                              |            |      4 |      1 |     2   (0)|      4 |00:00:00.01 |       0 |       |       |          |
|   9 |        RECURSIVE WITH PUMP                    |            |    120 |        |            |    116 |00:00:00.01 |       0 |       |       |          |
|  10 |     TABLE ACCESS FULL                         | HOLIDAYS   |      1 |      2 |    17   (0)|      1 |00:00:00.01 |      23 |       |       |          |
|* 11 |    INDEX UNIQUE SCAN                          | TIMEOFF_PK |     84 |      1 |     1   (0)|      1 |00:00:00.01 |      84 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter( IS NULL)
   3 - access("START_DATE"="H"."HOLIDAY_DATE")
   6 - filter(SUBSTR("E"."WORK_DAYS",TRUNC(INTERNAL_FUNCTION("START_DATE"))-TRUNC(INTERNAL_FUNCTION("START_DATE"),'fmiw')+1,1)='Y')
  11 - access("T"."EMPLOYEE_ID"=:B1 AND "T"."TIMEOFF_DATE"=:B2)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

As you can see, even though the query as written didn’t include the recursive CTE, the recursive query against DUAL appears in the plan. In fact the plan is exactly the same as the plan for the original query with the embedded CTE, though there is one interesting little difference – the generated query block names differ between plans.

Pros and Cons

Given that this is a lightweight example of a simple use of the SQL macro there’s not really a lot that can be said when comparing pipelined functions with macro functions. Both hide complexity and give you the opportunity to optimise an awkward piece of the code that might be (in effect) a common sub-routine.

The pipelined function does have to deal with the PL/SQL to SQL interchange – but that’s not a significant feature in this example. The main benefits, perhaps, of the macro are that the plan shows you the table(s) that would be hidden by the pipelined function, and may allow the optimizer to get better estimates of data sizes because it will be examining real tables with real statistics rather than taking a guess at a “pickler fetch” from a collection with a block box function.

Update (pre-publication)

There is some pleasure to be had by making mistakes in public, because that’s when you can learn something new. In my example to the OP on the Developer forum I used a much messier piece of code to embed the date values into the macro string, with lots of doubled and trebled quotes, to_char() functions, and concatenation all over the place.

Alex Nuijten replied to my suggestion pointing out that this degree of complexity was not necessary, and you could reference the functions parameters to construct the string. The only problem with that was that it hadn’t worked when I had tried it. Alex’s comment, however, also mentioned the problem and supplied the explanation: Bug 32212976: USING SCALAR ARGUMENTS IN WITH CLAUSE IN SQL TABLE MACRO RAISES ORA-06553 PLS-306 ). This was exactly the problem that I had been getting (the error message was – wrong number or types of arguments in call to ‘GENERATE_DATES_MACRO’ and I hadn’t thought about searching for known bugs or patches, I just hacked my way around the problem.

Here’s an alternative macro function supplied by Alex (edited slightly to be consistent with the function and column names in my example):

create or replace function generate_dates_macro(
    p_from in date,
    p_to  in date
)
return varchar2
sql_macro
is
    v_sql varchar2(4000);
begin
  v_sql := 'select trunc (generate_dates_macro.p_from) - 1 + level as day
       from dual
       connect by level <= (generate_dates_macro.p_to - generate_dates_macro.p_from) + 1';

--  dbms_output.put_line(v_sql);
    return v_sql;

end generate_dates_macro;
/

Test Code

If you want to experiment further, here’s the code to create the tables used in this demo:

rem
rem     Script:         19c_macro_2.sql
rem     Author:         Jonathan Lewis / "BeefStu"
rem     Dated:          July 2021
rem     Purpose:        
rem
rem     Last tested 
rem             19.11.0.0
rem
rem     Notes:
rem     A Macro solution to a problem that might
rem     otherwise be solved with a pipelined function
rem


drop table holidays;
drop table employees;
drop table timeoff;
drop table  emp_attendance;    
drop table absences;

drop function generate_dates_pipelined;
drop type nt_date;
drop type obj_date;

drop function generate_dates_macro;

-- @@setup

create table holidays(
        holiday_date    date,
        holiday_name    varchar2(20)
)
;

insert into holidays (holiday_date, holiday_name)
values ( to_date('2021/07/21 00:00:00', 'yyyy/mm/dd hh24:mi:ss'), 'July 21 2021') ;

create table employees(
        employee_id     number(6), 
        first_name      varchar2(20),
        last_name       varchar2(20),
        card_num        varchar2(10),
        work_days       varchar2(7)
)
;

alter table employees
        add constraint employees_pk primary key (employee_id)
;

insert into employees(employee_id, first_name, last_name, card_num, work_days)
with names as ( 
select 1, 'Jane', 'Doe', 'f123456', 'NYYYYYN' from dual 
union all 
select 2, 'Madison', 'Smith', 'r33432','NYYYYYN' from dual 
union all 
select 3, 'Justin', 'Case', 'c765341','NYYYYYN' from dual 
union all 
select 4, 'Mike', 'Jones', 'd564311','NYYYYYN' from dual 
) 
select * from names
;

create table timeoff(
        seq_num         integer generated by default as identity (start with 1) not null,
        employee_id     number(6),
        timeoff_date    date,
        timeoff_type    varchar2(1),
        constraint timeoff_chk check (timeoff_date = trunc(timeoff_date, 'dd')),
        constraint timeoff_pk primary key (employee_id, timeoff_date)
)
;

insert into timeoff (employee_id,timeoff_date,timeoff_type) 
with dts as ( 
select 1, to_date('20210726 00:00:00','yyyymmdd hh24:mi:ss'),'V'    from dual union all 
select 2, to_date('20210726 00:00:00','yyyymmdd hh24:mi:ss'),'V'    from dual union all 
select 2, to_date('20210727 00:00:00','yyyymmdd hh24:mi:ss'),'V'    from dual  
) 
select * from dts
;

create table  emp_attendance(    
        seq_num         integer  generated by default as identity (start with 1) not null,
        employee_id     number(6),
        start_date      date,
        end_date        date,
        week_number     number(2),
        create_date     date default sysdate
)
;

create table absences(
        seq_num         integer  generated by default as identity (start with 1) not null,
        employee_id     number(6),
        absent_date     date,
        constraint absence_chk check (absent_date=trunc(absent_date, 'dd')),
        constraint absence_pk primary key (employee_id, absent_date)
)
;

insert into emp_attendance (employee_id, start_date,end_date,week_number)
with dts as ( 
select 1, to_date('20210728 13:10:00','yyyymmdd hh24:mi:ss'), to_date('20210728 23:15:00','yyyymmdd hh24:mi:ss'), 30  from dual 
union all 
select 2, to_date('20210728 12:10:10','yyyymmdd hh24:mi:ss'), to_date('20210728 20:15:01','yyyymmdd hh24:mi:ss'), 30  from dual
)
select * from dts
;


7 Comments »

  1. Jonathan,

    It would be more precise to say that SQL *table* macros have been backported to recent releases of 19c. There are also SQL *scalar* macros, available starting in 21c.

    You say that “at run-time Oracle will execute the [SQL macro] function”. Later, you say “When we execute this statement Oracle evaluates the function…”

    Not true. The SQL macro is executed at *hard parse time*, not *run time*. You don’t see the difference because you call your function using literals, which are part of the SQL text that is parsed – and which are available to the parser. If you

    select * from generate_dates_macro(sysdate, sysdate+3) ;

    the generated SQL will contain

    to_date(”,’yyyy-mm-dd’)

    because the function arguments, not being literals, are ignored by the parser.

    The alternative by Alex works because the parameter *name* is interpreted as a placeholder. At parse time, the placeholder becomes a bind variable; at execution time, the parameter value is bound to that variable.

    I go into boring detail about table macro arguments here:

    SQL table macros 2: “polymorphic” is not “dynamic”

    Best regards,
    Stew Ashton

    Comment by stewashton — July 23, 2021 @ 7:32 am BST Jul 23,2021 | Reply

    • Stew,

      Thanks for the comments corrections and URL.
      I’m going to put a couple of corrections in-line.

      I would be very surprised if your detailed explanations will be boring – I shall be reading them as soon as I’ve finished the corrections above.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — July 26, 2021 @ 9:59 am BST Jul 26,2021 | Reply

  2. Hi

    I could see great benefits of SQL Macros
    – 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.

    However there are now some problems with SQL Macros. I currently have run at
    – CTE (with clause) does not operate very well with SQL macros if this parameter is used in CTE part of select statement. (Oracle support has told me that development is working with this, but didn’t tell the bug number).
    – We have some (old) very complex views, which are very long. Macros can use clob datatape as returning datatype, but it seems one just cannot convert the old views as string, because the the are so long.
    That would make converting these views rather complex and error prone operation.

    I regard SQL Macros has one great benefit over pipelined functions: if there are cases, that some other criteria is to be applied to view (e.g. CustomerNumber = ‘something’) this criteria can be applied in much earlier phase with sql macros than with pipelined functions.

    lh

    Comment by Anonymous — July 26, 2021 @ 7:06 am BST Jul 26,2021 | Reply

  3. lh,

    Thanks for the comment.

    It’s good to hear about problems (and opportunities) that people have seen from a new feature.

    The CTE issue is the reason why I’ve got the crude character substitution section in my code – I didn’t realise at the time that it was a known bug/limitation when I first tried it, and my workaround on the forum was even messier. And in the forum case the pipelined function was probably the better solution for the actual requirement anyway.

    The “very long complex view text” is a useful early warning. It sounds like a case for refactoring at the same time as applying a new feature – and that’s always a stressful thing to do, even though it’s often a good idea (in principle).

    Regards
    Jonathan Lewis

    Comment by Jonathan Lewis — July 26, 2021 @ 9:50 am BST Jul 26,2021 | Reply

  4. Jonathan,

    thanks for the nice blog post on sql macros, very usefull.

    when you say this “….In fact the table() operator hasn’t been needed since some time in the 12c timeline,…” what does it mean? dont we need “table()” clause when using table/pipelined table functions?

    Here is what i tried, but still dont understand..Kindly advice.

    demo@QES1> create or replace type ntt is table of number;
      2  /
    
    Type created.
    
    demo@QES1> create or replace function foo
      2  return ntt
      3  pipelined
      4  as
      5  begin
      6     for x in 1..5
      7     LOOP
      8             pipe row(x);
      9     end loop;
    10  end;
    11  /
    
    Function created.
    
    demo@QES1> select * from foo;
    select * from foo
                  *
    ERROR at line 1:
    ORA-04044: procedure, function, package, or type is not allowed here
    
    
    demo@QES1> select * from table(foo);
    
    COLUMN_VALUE
    ------------
               1
               2
               3
               4
               5
    
    demo@QES1> select banner_full from v$version;
    
    BANNER_FULL
    --------------------------------------------------------------------------------
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.9.0.0.0
    
    
    demo@QES1>
    

    Comment by Rajeshwaran Jeyabal — July 27, 2021 @ 12:33 pm BST Jul 27,2021 | Reply

    • Rajeshwaran,

      Thanks for the question.

      I think you could call this a case of parsing and error message not being completely up to date.

      Oracle needs to know in the SQL statement that foo is a function. Change the query to:

      select * from foo();
      

      and you will get the correct result.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — July 27, 2021 @ 1:10 pm BST Jul 27,2021 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: