stack of macro vitamins

When can I use SQL Macros?

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 🙂

image

There is also a MOS Note to assist with the data dictionary information about SQL macros.

image

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

8 comments

  1. 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?

  2. 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

    1. 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>

  3. 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

Got some thoughts? Leave a comment

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.