SQL Macros

 

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.