📋Steps in this guide1/8

SQL Macros in Oracle Database 21c

SQL Macros improve code reuse by factoring out common expressions and statements into reusable components.

oracle 21cconfigurationintermediate

by OracleDba

28 views

Setup

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

Code/Command (click line numbers to comment):

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

Code/Command (click line numbers to comment):

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>

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. When we use the table macro it works as expected. At first sight this looks like we've created a regular function, and the shared pool seems to suggest this also. 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. We turn on the 10053 trace, run the query and turn the trace off again. 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. 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.

Code/Command (click line numbers to comment):

create or replace function calculate_tax(p_value  number)
  return varchar2 sql_macro(scalar)
is
begin
  return q'{
    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>

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>

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>

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';

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

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 ( , , or ). Remember, this macro call is substituted into the SQL statement. SQL macros come in two forms. - Scalar Expressions. - Table Expressions. There are some points to consider about SQL macros, including restrictions. - They must return a string ( , , or ). - The clause can't be combined with , , or clauses. - Despite the clause not being allowed, all SQL macros are deterministic. - The property can't be specified, and the SQL macro always runs with invoker rights when used directed in a SQL statement. - SQL macros in views are always execute with the privileges of the view owner. - SQL macros can't be used in virtual column expression, function-based indexes, editioning views or materialized views. - SQL macros can't be used in type methods. - Scalar SQL macros can appear in a list, clause, or clause. - Table SQL macros can appear in a clause. - SQL scalar SQL macro can't have table arguments. - When a SQL macro is called directly from PL/SQL a string is returned. There is no macro expansion. 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 list, clause, and 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. We can incorporate SQL macro definitions into a package. The following example creates a package containing macros to display dates, datetimes and timestamps. We use the SQL macros in the normal way, remembering to prefix them with the package name. The following example prepares a name in a standard format for our application. 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 call to display those columns as a JSON fragment. 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.

Code/Command (click line numbers to comment):

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>

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;
/

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>

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>

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;
/

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 clause. We can think of this like building a query for an inline view. If you are trying to run these examples in Oracle 19c, alter "sql_macro(table)" to "sql_macro" to make them compile. Oracle 19c only supports table macros, and doesn't allow the explicit table annotation. Instead it relies on the default action when the SQL macro is not annotated, which assumes it is a table macro. The following example creates a table macro that returns the total salaries in each department. We can add this macro to a clause, just like we would a table function. The following example adds a parameter to the table macro. We can now limit the output by the department number. We can include joins in the table macro. The following example joins the departments table, to pull back the department name. A table macro can accept table arguments, so a single SQL macro can perform an action against multiple tables. Within the table macro we have access to information about the table using the type. In the following example we use the table column names to build a query using the function, which returns a JSON fragment for each row. 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 type.

Code/Command (click line numbers to comment):

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;
/

select * from sal_by_dept();

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

SQL>

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>

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>

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>

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>

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 column in the 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. The SQL macro source is present in the views, like any other code-based object. They can also be displayed using the package, like most other objects.

Code/Command (click line numbers to comment):

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>

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>

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. - If you have a piece of functionality that is required from both SQL and PL/SQL, you may prefer to use a regular function over a SQL macro. The SQL macro could only be used in PL/SQL as part of a query. For example you might do a to make an assignment. This is better than having duplicate functionality for SQL and PL/SQL, but some people may not find it acceptable. - There are alternatives to reduce the impact of context switches. In the example of the scalar SQL macro, the function could have been implemented using to reduce the impact of the context switches. You can read more about here . - There are also a number of caching methods that can increase the performance of function calls in SQL, and may be more appropriate than a scalar SQL macro in some circumstances. You can read more about efficient function calls from SQL here . Thanks to @mortenbraten , @DVanhaute , @ChrisRSaxon and @StewAshton for their feedback. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!