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