DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on Edited on

Simplified SQL Macros in Oracle AI Database 23.26.4

#oracle #database #sql #sqlmacros

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.

1. Creating a Scalar SQL Macro

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

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

The invocation remains unchanged:

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

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.

2. Creating a Table SQL Macro

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
Enter fullscreen mode Exit fullscreen mode 
SQL> SELECT * FROM table_macro_Mesal('SYS');
   SIZE_MB
----------
       168
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

We can invoke the macro in exactly the same way:

SQL> SELECT * FROM table_macro_Mesal('SYS');
   SIZE_MB
----------
       168
Enter fullscreen mode Exit fullscreen mode

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

3. Passing Multiple Parameters to a Table SQL Macro

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
Enter fullscreen mode Exit fullscreen mode 
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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 
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
Enter fullscreen mode Exit fullscreen mode

4. Additional Capabilities of Native SQL Macros

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
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)