Simplified SQL Macros in Oracle AI Database 23.26.4

SQL Macros, introduced in Oracle Database 21c, allow developers to create reusable SQL expressions and queries. I previously covered this feature in my article SQL Macros in Oracle Database 21c.

Previously, creating SQL Macros required PL/SQL functions that returned SQL statements as strings.

Oracle AI Database 23.26.4 introduces Simplified Macros, allowing developers to define macros directly using the new CREATE MACRO statement without writing PL/SQL code.

In this article, we will explore this enhancement and rewrite some of the examples from my previous article using the new syntax.

Let’s start with a simple example.

Suppose we have a table named PERSON and want to add 60 to the salary of each employee.

In earlier releases, we could define a Scalar SQL Macro using a PL/SQL function:

SQL> CREATE OR REPLACE FUNCTION sal_func(
    sal_param NUMBER
)
RETURN VARCHAR2 SQL_MACRO(SCALAR)
IS
BEGIN
    RETURN q'{
        sal_param + 60
    }';
END;
/
Function created

We could then use this function in a SQL statement:

SQL> select sum(sal) from(select salary+60 as sal from person);
  SUM(SAL)
----------
       197

With Oracle AI Database 23.26.4, we can define the same macro directly using SQL:

SQL> drop FUNCTION sal_func;
Function dropped
SQL> CREATE MACRO sal_func(sal_param NUMBER) AS
        sal_param + 60;
Done

The invocation remains unchanged:

SQL> select sum(sal) from(select salary+60 as sal from person);
  SUM(SAL)
----------
       197

As you can see, we no longer need to create a PL/SQL function, specify SQL_MACRO(SCALAR), or return the SQL expression as a character string.

SQL Macros can also return SQL queries and be used in the FROM clause.

Consider the following example from my previous article. This macro returns the size, in MB, of the largest segment belonging to a specified schema.

In earlier releases, we could implement it as follows:

SQL>CREATE OR REPLACE FUNCTION table_macro_Mesal(
    p_tab VARCHAR2
)
RETURN VARCHAR2 SQL_MACRO(TABLE)
IS
BEGIN
    RETURN q'{
        SELECT MAX(bytes/1024/1024) SIZE_MB
        FROM dba_segments d
        WHERE owner = table_macro_Mesal.p_tab
    }';
END;
/
Function created
SQL> SELECT * FROM table_macro_Mesal('SYS');
   SIZE_MB
----------
       168

With the new syntax, the definition becomes much simpler:

SQL>CREATE MACRO table_macro_Mesal(p_tab VARCHAR2) AS
    SELECT MAX(bytes/1024/1024) SIZE_MB
    FROM dba_segments d
    WHERE owner = table_macro_Mesal.p_tab;
Done

We can invoke the macro in exactly the same way:

SQL> SELECT * FROM table_macro_Mesal('SYS');
   SIZE_MB
----------
       168

The macro accepts a schema name as its parameter and returns a query that calculates the maximum segment size for that schema.

The new syntax also supports multiple parameters.

Let’s consider another example from my previous article. This macro accepts two parameters:

• cnt: The number of segments to return.

• typ: The segment type used to filter the results.

The original function-based implementation was:

SQL> CREATE OR REPLACE FUNCTION table_macro_Mesal2(
    cnt NUMBER,
    typ VARCHAR2
)
RETURN VARCHAR2 SQL_MACRO(TABLE)
IS
BEGIN
    RETURN q'{
        SELECT owner,
               segment_name,
               bytes / 1024 / 1024 SIZE_MB
        FROM dba_segments
        WHERE segment_type = table_macro_Mesal2.typ
        ORDER BY bytes DESC
        FETCH FIRST cnt ROWS ONLY
    }';
END;
/
Function created
SQL> SELECT * FROM table_macro_Mesal2(5, 'TABLE');
OWNER  SEGMENT_NAME            SIZE_MB
------ -------------------- ----------
SYS    IDL_UB2$                     18
SYS    IDL_UB1$                     15
SYS    OBJ$                         11
MDSYS  SDO_CS_SRS                    9
SYS    DEPENDENCY$                   6

Using the new CREATE MACRO statement, we can define it as follows:

SQL> CREATE MACRO table_macro_Mesal2(
    cnt NUMBER,
    typ VARCHAR2
) AS
    SELECT owner,
           segment_name,
           bytes / 1024 / 1024 SIZE_MB
    FROM dba_segments
    WHERE segment_type = table_macro_Mesal2.typ
    ORDER BY bytes DESC
    FETCH FIRST table_macro_Mesal2.cnt ROWS ONLY;
Done
SQL> SELECT * FROM table_macro_Mesal2(5, 'TABLE');
OWNER  SEGMENT_NAME            SIZE_MB
------ -------------------- ----------
SYS    IDL_UB2$                     18
SYS    IDL_UB1$                     15
SYS    OBJ$                         11
MDSYS  SDO_CS_SRS                    9
SYS    DEPENDENCY$                   6

Oracle AI Database 23.26.4 introduces additional capabilities for native SQL Macros, including default parameter values, table parameters, reusable predicates, and binding macros to specific tables or views.

Oracle also provides new data dictionary views, such as USER_MACROS and USER_MACRO_PARAMETERS, to inspect macro definitions and their parameters.

SQL> select owner,name,param_count from USER_MACROS;
OWNER  NAME                  PARAM_COUNT
------ --------------------  -----------
VAHID  SAL_FUNC                        1
VAHID  TABLE_MACRO_MESAL               1
VAHID  TABLE_MACRO_MESAL2              2

I will explore these capabilities in more detail in future articles.