SQL Macros was introduced in Oracle 21c. There are two types of SQL Macros in 21c ( Scalar and Table). These enhancements make it easy to implement a view , where we can pass in the parameter to get the desired results.
Prior to this , we can achieve this by using parameterized views and contexts. It is a bit cumbersome. This feature does not involve context switching.
This feature is backported to 19c. But only "Table type SQL Macro".
As 21c release is the innovation release , Oracle recommends 19c to be the production release.
We create a function , and indicate that it returns TABLE for Macro Type.
Then we can use this , FROM clause of the SELECT statement as shown below.
SQL Macro - Table
=================
SQL> set linesize 1000
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
SQL> create table t as select * from all_objects;
Table created.
SQL> CREATE OR replace FUNCTION count_objects_table (
2 obj_owner_name VARCHAR2
3 ) RETURN VARCHAR2
4 SQL_MACRO( TABLE ) is begin RETURN q'{
5 Select object_type , count(*) as Count_Type from t
6 Where owner = count_objects_table.obj_owner_name
7 group by object_type
8 }'
9 ;
10 END;
11 /
Function created.
SQL> set pagesize 1000
SQL> SELECT
2 *
3 FROM
4 count_objects_table ( 'SYS' );
OBJECT_TYPE COUNT_TYPE
----------------------- ----------
INDEX 1623
TABLE 1750
CLUSTER 10
EDITION 1
SEQUENCE 3
DIRECTORY 12
PACKAGE 424
VIEW 7000
FUNCTION 103
SYNONYM 16
PROCEDURE 25
TYPE 1149
OPERATOR 7
TABLE PARTITION 466
INDEX PARTITION 276
TABLE SUBPARTITION 32
CONSUMER GROUP 18
JOB CLASS 3
DESTINATION 2
SCHEDULE 4
WINDOW 9
SCHEDULER GROUP 4
EVALUATION CONTEXT 1
JAVA CLASS 35259
JAVA RESOURCE 1658
25 rows selected.
SQL Macro - Scalar
=================
Here , we will create a function that returns scalar macro.
And then use it in SELECT clause , as shown below.
SQL> CREATE OR replace FUNCTION calculate_pies (
2 p_arg1 NUMBER
3 ) RETURN VARCHAR2 sql_macro ( scalar ) is
4 begin
5 return q'{
6 p_arg1 * 3.14
7 }';
8 end;
9 /
Function created.
SQL> SELECT
2 level,
3 calculate_pies(level)
4 FROM
5 dual
6 CONNECT BY
7 level <= 5;
LEVEL CALCULATE_PIES(LEVEL)
---------- ---------------------
1 3.14
2 6.28
3 9.42
4 12.56
5 15.7
In 19c
------
As the "table" SQL macro is only the type supported in 19c , the following scipt produces error.
By removing the table type , the function is valid and usable.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> CREATE OR replace FUNCTION count_objects_table (
2 obj_owner_name VARCHAR2
3 ) RETURN VARCHAR2
4 SQL_MACRO( TABLE ) is begin RETURN q'{
5 Select object_type , count(*) as Count_Type from all_objects
6 Where owner = count_objects_table.obj_owner_name
7 group by object_type
8 }'
9 ;
10 END;
11 /
Warning: Function created with compilation errors.
SQL> show errors
Errors for FUNCTION COUNT_OBJECTS_TABLE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/10 PLS-00103: Encountered the symbol "(" when expecting one of the
following:
; is default authid as cluster order using external
deterministic parallel_enable pipelined aggregate
result_cache accessible rewrite
SQL> CREATE OR replace FUNCTION count_objects_table (
2 obj_owner_name VARCHAR2
3 )
4 return VARCHAR2
5 sql_macro
6 IS
7 BEGIN
8 RETURN q'{
9 Select object_type , count(*) as Count_Type from all_objects
10 Where owner = count_objects_table.obj_owner_name
11 group by object_type
12 }'
13 ;
14 END;
15 /
Function created.
SQL> set pagesize 100
SQL> Select * From count_objects_table('SYS');
OBJECT_TYPE COUNT_TYPE
----------------------- ----------
SEQUENCE 4
PROCEDURE 20
TABLE SUBPARTITION 32
FUNCTION 101
INDEX PARTITION 130
WINDOW 9
JAVA RESOURCE 1640
JOB CLASS 2
EVALUATION CONTEXT 1
JAVA CLASS 34395
PACKAGE 261
VIEW 6976
TABLE 1575
EDITION 1
SYNONYM 16
TYPE 1104
SCHEDULE 4
SCHEDULER GROUP 4
CLUSTER 10
OPERATOR 7
TABLE PARTITION 326
INDEX 1454
CONSUMER GROUP 18
DESTINATION 2
24 rows selected.
SQL Macro is a welcome addition to Oracle. It eases some of the complexity.
Similarly , I hope , Oracle adds "Filtered Index" ( as it exists in SQL Server) to its feature list in the coming releases.
Comments Welcome.
