8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 21c » Here

SQL Macros in Oracle Database 21c

SQL Macros improve code reuse by factoring out common expressions and statements into reusable components. SQL Macros for scalar and table expressions were introduced in the Oracle database 20c preview release, and were made generally available in Oracle 21c. SQL Macros for table expressions were backport ported to 19c in the 19.6 release update.

Related articles.

Setup

Some of the examples in this article use the following tables.

-- drop table emp purge;
-- drop table dept purge;

create table dept (
  deptno number(2) constraint pk_dept primary key,
  dname varchar2(14),
  loc varchar2(13)
) ;

create table emp (
  empno number(4) constraint pk_emp primary key,
  ename varchar2(10),
  job varchar2(9),
  mgr number(4),
  hiredate date,
  sal number(7,2),
  comm number(7,2),
  deptno number(2) constraint fk_deptno references dept
);

insert into dept values (10,'ACCOUNTING','NEW YORK');
insert into dept values (20,'RESEARCH','DALLAS');
insert into dept values (30,'SALES','CHICAGO');
insert into dept values (40,'OPERATIONS','BOSTON');

insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
insert into emp values (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;

The Problem

We have several queries in an application that need to calculate tax. If the tax rate changes, that means making code changes in each query. The typical solution to this issue is to create a function to calculate tax. This function can be used in all the queries and therefore centralises the tax calculation. In the example below we create a function that calculates the tax at 20%.

create or replace function calculate_tax(p_value  number)
  return number
is
begin
  return p_value * 0.2;
end;
/


select sal, calculate_tax(sal) as tax from emp where deptno = 10;

       SAL        TAX
---------- ----------
      2450        490
      5000       1000
      1300        260

SQL>

That works fine, but calling a function from SQL adds an overhead. Each time we switch between the SQL engine to the PL/SQL engine and back we burn some CPU. Depending on the nature of the query, this overhead could be significant.

The Solution (SQL Macros)

SQL Macros are one of several possible solutions. I'll mention some others in the Considerations section.

SQL macros look similar to conventional functions, but rather than performing an action during query execution, their action occurs during the optimization of the query. They explain how to rewrite the macro call with the expression returned by the macro. The optimizer does this substitution before executing the query.

The following SQL macro returns a scalar expression that calculates tax at 20% of the input parameter.

create or replace function calculate_tax(p_value  number)
  return varchar2 sql_macro(scalar)
is
begin
  return q'{
    p_value * 0.2
  }';
end;
/

When we use the table macro it works as expected.

select sal, calculate_tax(sal) as tax from emp where deptno = 10;

       SAL        TAX
---------- ----------
      2450        490
      5000       1000
      1300        260

SQL>

At first sight this looks like we've created a regular function, and the shared pool seems to suggest this also.

select sql_text
from   v$sqlarea
where  sql_text like '%tax%'
and    sql_text not like '%sqlarea%';

SQL_TEXT
--------------------------------------------------------------------------------
select sal, calculate_tax(sal) as tax from emp where deptno = 10

SQL>

To see what is really happening, we need to do a 10053 trace on the statement. We flush the shared pool and reconnect to our test user, then check the name of the trace file for the current session.

conn sys@pdb1 as sysdba
alter system flush shared_pool;


conn testuser1@pdb1

select value
from   v$diag_info
where  name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb1_lhr12p/cdb1/trace/cdb1_ora_26425.trc

SQL>

We turn on the 10053 trace, run the query and turn the trace off again.

alter session set events '10053 trace name context forever';

select sal, calculate_tax(sal) as tax from emp where deptno = 10;

alter session set events '10053 trace name context off';

We check the section of the trace file beginning with "Final query after transformations" and we see the following. Notice there is no function call present in the statement. Instead it has the calculation substituted into the statement.

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "EMP"."SAL" "SAL","EMP"."SAL"*0.2 "TAX" FROM "TESTUSER1"."EMP" "EMP" WHERE "EMP"."DEPTNO"=10

So we still have the centralised approach to calculating tax, but we're not adding the overhead of making a function call. We have the same performance as doing the calculation directly in the query.

If we call the SQL macro directly from PL/SQL, we see the macro string is produced. There is no macro expansion.

SQL> exec dbms_output.put_line(calculate_tax(100));

    p_value * 0.2

PL/SQL procedure successfully completed.

SQL>

Basics

SQL macros look similar to normal functions, but they must return a string (VARCHAR2, CHAR, or CLOB). Remember, this macro call is substituted into the SQL statement.

SQL macros come in two forms.

There are some points to consider about SQL macros, including restrictions.

SQL macros are just producing text, so it's easy to enter incorrect syntax and the macro will compile. It's only at runtime you will see an error, so test your macros carefully.

SQL Macros : Scalar Expressions

SQL macros for scalar expressions return an expression that is substituted into the SQL Statement in place of the SQL macro call. These can be used in the SELECT list, WHERE clause, and HAVING clause.

We've already seen an example of a SQL macros using a scalar expression in the section above, but lets try a few more examples.

The following example allows us to define a standard format to output dates in our application.

create or replace function show_date(p_value  date)
  return varchar2 sql_macro(scalar)
is
begin
  return q'{ to_char(p_value, 'YYYY-MM-DD') }';
end;
/


select show_date(sysdate) as my_date from dual;

MY_DATE
----------
2020-12-26

SQL>

We can incorporate SQL macro definitions into a package. The following example creates a package containing macros to display dates, datetimes and timestamps.

create or replace package date_macros as

  function show_date(p_value  date)
    return varchar2 sql_macro(scalar);

  function show_datetime(p_value  date)
    return varchar2 sql_macro(scalar);

  function show_timestamp(p_value  timestamp)
    return varchar2 sql_macro(scalar);

end;
/


create or replace package body date_macros as

function show_date(p_value  date)
  return varchar2 sql_macro(scalar)
is
begin
  return q'{ to_char(p_value, 'YYYY-MM-DD') }';
end;

function show_datetime(p_value  date)
  return varchar2 sql_macro(scalar)
is
begin
  return q'{ to_char(p_value, 'YYYY-MM-DD HH24:MI:SS') }';
end;

function show_timestamp(p_value  timestamp)
  return varchar2 sql_macro(scalar)
is
begin
  return q'{ to_char(p_value, 'YYYY-MM-DD HH24:MI:SS.FF') }';
end;

end;
/

We use the SQL macros in the normal way, remembering to prefix them with the package name.

select date_macros.show_date(sysdate) as my_date from dual;

MY_DATE
----------
2020-12-26

SQL>


select date_macros.show_datetime(sysdate) as my_datetime from dual;

MY_DATETIME
-------------------
2020-12-26 21:06:41

SQL>


select date_macros.show_timestamp(systimestamp) as my_timestamp from dual;

MY_TIMESTAMP
-----------------------------
2020-12-26 21:07:19.438043

SQL>

The following example prepares a name in a standard format for our application.

create or replace function show_full_name(
  p_first_name    varchar2,
  p_middle_names  varchar2,
  p_last_name     varchar2
)
  return varchar2 sql_macro(scalar)
is
  l_sql  varchar2(32767);
begin
  l_sql := q'{ p_first_name || ' ' || nvl2(p_middle_names, p_middle_names||' ', '') || p_last_name }';
  return l_sql;
end;
/


select show_full_name('Wonder', null, 'Woman') as full_name from dual;

FULL_NAME
------------
Wonder Woman

SQL>


select show_full_name('Conan', 'the', 'Barbarian') as full_name from dual;

FULL_NAME
-------------------
Conan the Barbarian

SQL>

When you are coding scalar macros, remember you don't have access to parameter values. The expression you build is done at optimization time, not runtime, so your expression needs to cope with all scenarios in advance of knowing the values.

A scalar SQL macro can accept column attributes, which allows us to do some interesting things. The following scalar SQL macro accepts column arguments, and uses those to return the correct JSON_OBJECT call to display those columns as a JSON fragment.

create or replace function get_json (p_cols dbms_tf.columns_t)
  return clob sql_macro(scalar)
is
  l_columns varchar2(32767);
begin
  -- Get comma-separated list of column names in lower case. Remove quotes.
  for i in 1 .. p_cols.count loop
    l_columns := l_columns || trim(both '"' from lower(p_cols(i))) || ',';
  end loop;
  l_columns := rtrim(l_columns, ',');

  return 'json_object(' || l_columns || ')';
end;
/

We can now use the macro in a select list, passing a list of columns we want to be included in the JSON document. Below are two examples.

column json_data format a50

select empno, get_json(columns(ename, job, sal)) as json_data from emp where deptno = 20;

     EMPNO JSON_DATA
---------- --------------------------------------------------
      7369 {"ename":"SMITH","job":"CLERK","sal":800}
      7566 {"ename":"JONES","job":"MANAGER","sal":2975}
      7788 {"ename":"SCOTT","job":"ANALYST","sal":3000}
      7876 {"ename":"ADAMS","job":"CLERK","sal":1100}
      7902 {"ename":"FORD","job":"ANALYST","sal":3000}

SQL>


select empno, get_json(columns(empno, ename)) as json_data from emp where deptno = 20;

     EMPNO JSON_DATA
---------- --------------------------------------------------
      7369 {"empno":7369,"ename":"SMITH"}
      7566 {"empno":7566,"ename":"JONES"}
      7788 {"empno":7788,"ename":"SCOTT"}
      7876 {"empno":7876,"ename":"ADAMS"}
      7902 {"empno":7902,"ename":"FORD"}

SQL>

SQL Macros : Table Expressions

SQL macros for table expressions return a table expression that is substituted into the SQL statement in place of the SQL macro. These are used in the FROM clause. We can think of this like building a query for an inline view.

The following example creates a table macro that returns the total salaries in each department.

create or replace function sal_by_dept
  return varchar2 sql_macro(table)
is
begin
  return q'{
    select deptno, sum(sal) as sal_tot
    from   emp 
    group by deptno
  }';
end;
/

We can add this macro to a FROM clause, just like we would a table function.

select * from sal_by_dept();

    DEPTNO    SAL_TOT
---------- ----------
        20      10875
        30       9400
        10       8750

SQL>

The following example adds a parameter to the table macro. We can now limit the output by the department number.

create or replace function sal_by_dept (p_deptno number)
  return varchar2 sql_macro(table)
is
begin
  return q'{
    select deptno, sum(sal) as sal_tot
    from   emp
    where  deptno = p_deptno
    group by deptno
  }';
end;
/


select * from sal_by_dept(10);

    DEPTNO    SAL_TOT
---------- ----------
        10       8750

SQL>

We can include joins in the table macro. The following example joins the departments table, to pull back the department name.

create or replace function sal_by_dept (p_deptno number)
  return varchar2 sql_macro(table)
is
begin
  return q'{
    select e.deptno, d.dname, sum(e.sal) as sal_tot
    from   emp e
           join dept d on e.deptno = d.deptno
    where  e.deptno = p_deptno
    group by e.deptno, d.dname
  }';
end;
/


select * from sal_by_dept(10);

    DEPTNO DNAME             SAL_TOT
---------- -------------- ----------
        10 ACCOUNTING           8750

SQL>

A table macro can accept table arguments, so a single SQL macro can perform an action against multiple tables.

create or replace function row_count (p_tab dbms_tf.table_t)
  return varchar2 sql_macro(table)
is
begin
  return q'{
    select count(*) as row_count from p_tab
  }';
end;
/


select * from row_count(emp);

 ROW_COUNT
----------
        14

SQL>


select * from row_count(dept);

 ROW_COUNT
----------
         4

SQL>

Within the table macro we have access to information about the table using the DBMS_TF.TABLE_T type. In the following example we use the table column names to build a query using the JSON_OBJECT function, which returns a JSON fragment for each row.

create or replace function get_json (p_tab dbms_tf.table_t)
  return clob sql_macro(table)
is
  l_columns varchar2(32767);
  l_sql     varchar2(32767);
begin
  for i in 1 .. p_tab.column.count loop
    l_columns := l_columns || trim(both '"' from lower(p_tab.column(i).description.name)) || ',';
  end loop;
  l_columns := rtrim(l_columns, ',');

  l_sql := 'select json_object(' || l_columns || ') as json_data from p_tab';
  return l_sql;
end;
/


select * from get_json(dept);

JSON_DATA
--------------------------------------------------------------------------------
{"deptno":10,"dname":"ACCOUNTING","loc":"NEW YORK"}
{"deptno":20,"dname":"RESEARCH","loc":"DALLAS"}
{"deptno":30,"dname":"SALES","loc":"CHICAGO"}
{"deptno":40,"dname":"OPERATIONS","loc":"BOSTON"}

SQL>


select * from get_json(emp);

JSON_DATA
------------------------------------------------------------------------------------------------------------------------------------------------------
{"empno":7369,"ename":"SMITH","job":"CLERK","mgr":7902,"hiredate":"1980-12-17T00:00:00","sal":800,"comm":null,"deptno":20}
{"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-20T00:00:00","sal":1600,"comm":300,"deptno":30}
{"empno":7521,"ename":"WARD","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-22T00:00:00","sal":1250,"comm":500,"deptno":30}
{"empno":7566,"ename":"JONES","job":"MANAGER","mgr":7839,"hiredate":"1981-04-02T00:00:00","sal":2975,"comm":null,"deptno":20}
{"empno":7654,"ename":"MARTIN","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-28T00:00:00","sal":1250,"comm":1400,"deptno":30}
{"empno":7698,"ename":"BLAKE","job":"MANAGER","mgr":7839,"hiredate":"1981-05-01T00:00:00","sal":2850,"comm":null,"deptno":30}
{"empno":7782,"ename":"CLARK","job":"MANAGER","mgr":7839,"hiredate":"1981-06-09T00:00:00","sal":2450,"comm":null,"deptno":10}
{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-19T00:00:00","sal":3000,"comm":null,"deptno":20}
{"empno":7839,"ename":"KING","job":"PRESIDENT","mgr":null,"hiredate":"1981-11-17T00:00:00","sal":5000,"comm":null,"deptno":10}
{"empno":7844,"ename":"TURNER","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-08T00:00:00","sal":1500,"comm":0,"deptno":30}
{"empno":7876,"ename":"ADAMS","job":"CLERK","mgr":7788,"hiredate":"1987-05-23T00:00:00","sal":1100,"comm":null,"deptno":20}
{"empno":7900,"ename":"JAMES","job":"CLERK","mgr":7698,"hiredate":"1981-12-03T00:00:00","sal":950,"comm":null,"deptno":30}
{"empno":7902,"ename":"FORD","job":"ANALYST","mgr":7566,"hiredate":"1981-12-03T00:00:00","sal":3000,"comm":null,"deptno":20}
{"empno":7934,"ename":"MILLER","job":"CLERK","mgr":7782,"hiredate":"1982-01-23T00:00:00","sal":1300,"comm":null,"deptno":10}

14 rows selected.

SQL>

If we didn't care about the case of the elements, we could have done the following, but uppercase element names are ugly, and it wouldn't demonstrate the use of the DBMS_TF.TABLE_T type.

create or replace function get_json (p_tab dbms_tf.table_t)
  return clob sql_macro(table)
is
begin
  return 'select json_object(*) from p_tab';
end;
/


set linesize 150

select * from get_json(dept);

JSON_OBJECT(*)
--------------------------------------------------------------------------------
{"DEPTNO":10,"DNAME":"ACCOUNTING","LOC":"NEW YORK"}
{"DEPTNO":20,"DNAME":"RESEARCH","LOC":"DALLAS"}
{"DEPTNO":30,"DNAME":"SALES","LOC":"CHICAGO"}
{"DEPTNO":40,"DNAME":"OPERATIONS","LOC":"BOSTON"}

SQL>

Views

The SQL_MACRO column in the {CDB|DBA|ALL|USER}_PROCEDURES views can be used to display SQL macros. The column contains the values "TABLE" or "SCALAR" depending on the type of macro, or the string "NULL" if the object is not a SQL macro.

set linesize 120 pagesize 20
column object_name format a30
column procedure_name format a30
column sql_macroformat a9

select uo.object_type,
       up.sql_macro,
       up.object_name,
       up.procedure_name
from   user_procedures up
       join user_objects  uo on up.object_id = uo.object_id
where  up.sql_macro != 'NULL'
order by uo.object_type, up.sql_macro, up.object_name, up.procedure_name;

OBJECT_TYPE             SQL_MA OBJECT_NAME                    PROCEDURE_NAME
----------------------- ------ ------------------------------ ------------------------------
FUNCTION                SCALAR CALCULATE_TAX
FUNCTION                SCALAR SHOW_DATE
FUNCTION                SCALAR SHOW_FULL_NAME
FUNCTION                TABLE  GET_JSON
FUNCTION                TABLE  ROW_COUNT
FUNCTION                TABLE  SAL_BY_DEPT
PACKAGE                 SCALAR DATE_MACROS                    SHOW_DATE
PACKAGE                 SCALAR DATE_MACROS                    SHOW_DATETIME
PACKAGE                 SCALAR DATE_MACROS                    SHOW_TIMESTAMP

9 rows selected.

SQL>

The SQL macro source is present in the {CDB|DBA|ALL|USER}_SOURCE views, like any other code-based object.

column text format a50

select line, text
from   user_source
where  name = 'CALCULATE_TAX'
order  by line;

      LINE TEXT
---------- --------------------------------------------------
         1 function calculate_tax(
         2   p_value  number
         3 )
         4   return varchar2 sql_macro(scalar)
         5 is
         6 begin
         7   return q'{
         8     p_value * 0.2
         9   }';
        10 end;

10 rows selected.

SQL>

They can also be displayed using the DBMS_METADATA package, like most other objects.

set long 20000

select dbms_metadata.get_ddl('FUNCTION', 'CALCULATE_TAX') as function_ddl
from   dual;

FUNCTION_DDL
--------------------------------------------------------------------------------

  CREATE OR REPLACE EDITIONABLE FUNCTION "TESTUSER1"."CALCULATE_TAX" (
  p_value  number
)
  return varchar2 sql_macro(scalar)
is
begin
  return q'{
    p_value * 0.2
  }';
end;


SQL>

Considerations

SQL macros are one of several solutions for improving the performance of function calls from SQL. It's important you don't think of them in isolation.

Thanks to @mortenbraten, @DVanhaute, @ChrisRSaxon and @StewAshton for their feedback.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.