SQL_MACRO Clause Oracle Database 21C

This article describes nice new feature SQL_MACRO that allows to simplify coding in PL/SQL for table expressions.

It’s a new, simpler way to encapsulate complex processing logic directly within SQL. SQL Macros allow developers to encapsulate complex processing within a new structure called a “macro” which can then be used within SQL statement. Essentially there two types of SQL Macros: SCALAR and TABLE. What’s the difference:

  • SCALAR expressions can be used in SELECT list, WHERE/HAVING, GROUP BY/ORDER BY clauses  
  • TABLE expressions used in a FROM-clause

Let’s prepare test data first

CREATE TABLE test_tbl
(
id NUMBER,
name VARCHAR2(30)
);

INSERT INTO test_tbl VALUES(1, 'TOMASZ');
INSERT INTO test_tbl VALUES(1, 'TOMASZ');
INSERT INTO test_tbl VALUES(1, 'GREG');
INSERT INTO test_tbl VALUES(1, 'GREG');
INSERT INTO test_tbl VALUES(1, 'CHRIS');
INSERT INTO test_tbl VALUES(1, 'ADAM');
INSERT INTO test_tbl VALUES(2, 'ADAM');
INSERT INTO test_tbl VALUES(2, 'ADAM');

COMMIT;

SELECT * FROM test_tbl;

ID NAME
-- -------
1 TOMASZ
1 TOMASZ
1 GREG
1 GREG
1 CHRIS
1 ADAM
2 ADAM
2 ADAM

SQL_MACRO Table

Then we build simple function fn_group_by with parameter p_name that returns type VARCHAR2 SQL_MACRO 

CREATE OR REPLACE FUNCTION fn_group_by
(
p_name VARCHAR2
)
RETURN VARCHAR2 SQL_MACRO
IS
BEGIN
RETURN q'{SELECT name, SUM(id) id
FROM test_tbl
WHERE name = fn_group_by.p_name
GROUP BY name}';
END;
/

When the function is executed query inside function fn_group_by  is executed dynamically and data is returned. It’s very simple nice way to build dynamic parametrized queries !!!

select * from fn_group_by('TOMASZ');

NAME ID
------------------------------ ----------
TOMASZ 2

select * from fn_group_by('GREG');

NAME ID
------------------------------ ----------
GREG 2

You can easily use it inside PL/SQL

BEGIN
FOR I IN (SELECT * FROM fn_group_by('GREG'))
LOOP
NULL;
END LOOP;

FOR I IN (SELECT * FROM fn_group_by('TOMASZ'))
LOOP
NULL;
END LOOP;
END;
/

or build views on top of that

create view test_tbl_vw
as
SELECT * FROM fn_group_by('GREG');

desc test_tbl_vw

Name Null? Type
---- ----- ------------
NAME VARCHAR2(30)
ID NUMBER

It’s transparent in explain plan

explain plan for
select * from fn_group_by('GREG');

select *
from table(dbms_xplan.display);

Plan hash value: 148626839

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 60 | 3 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 2 | 60 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | TEST_TBL | 2 | 60 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

2 - filter("NAME"='GREG')

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

SQL_MACRO Scalar

it works a little bit different than SQL_MACRO Table. It can transform part of your query when you execute it.

CREATE OR REPLACE FUNCTION fn_scalar(p_name VARCHAR2) 
RETURN VARCHAR2 SQL_MACRO(scalar)
IS
BEGIN
RETURN q'{dump(fn_scalar.p_name)}';
END;
/

and use it in query

select id, fn_scalar(name) from test_tbl;

ID FN_SCALAR(NAME)
1 Typ=1 Len=6: 84,79,77,65,83,90
1 Typ=1 Len=6: 84,79,77,65,83,90
1 Typ=1 Len=4: 71,82,69,71
1 Typ=1 Len=4: 71,82,69,71
1 Typ=1 Len=5: 67,72,82,73,83
1 Typ=1 Len=4: 65,68,65,77
2 Typ=1 Len=4: 65,68,65,77
2 Typ=1 Len=4: 65,68,65,77

you can display what SQL_MACRO(scalar) returns

set serveroutput on
exec dbms_output.put_line(fn_scalar('test text'));

dump(fn_scalar.p_name)

it could be useful everywhere where you want to hide complex transformation.

nice feature of SQL_MACRO(scalar) is possibility to pass as argument dynamic list of columns and manipulate them


CREATE OR REPLACE FUNCTION fn_gen_csv
(
p_cols dbms_tf.columns_t
)
RETURN CLOB SQL_MACRO(SCALAR)
IS
l_cols CLOB;
BEGIN
FOR i IN 1 .. p_cols.COUNT
LOOP
l_cols := l_cols || '||'',''||' || p_cols(I);
END LOOP;

l_cols := LTRIM(l_cols, '||'',''||');

dbms_output.put_line(l_cols);

RETURN l_cols;
END;
/

select FN_GEN_CSV(columns(id, name)) from test_tbl a;

FN_GEN_CSV(COLUMNS(ID,NAME))
1,TOMASZ
1,TOMASZ
1,GREG
1,GREG
1,CHRIS
1,ADAM
2,ADAM
2,ADAM

Features of SQL_MACRO

  • The SQL_MACRO annotation is disallowed with RESULT_CACHE, PARALLEL_ENABLE, and PIPELINED.
  • Although the DETERMINISTIC property cannot be specified, a SQL macro is always implicitly deterministic.
  • SQL macros can be used in any SQL statement, including DML, DDL, and DCL statements.
  • SQL macros can be nested.
  • SQL macros can be used to dynamically generate SQL statements.
  • SQL macros can be used to improve performance by caching the results of expensive queries.

Have fun 🙂

  Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *

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