Simplified SQL Macros in Oracle AI Database 23.26.4

Oracle AI Database 23.26.4 introduces Simplified SQL Macros, letting developers define scalar and table macros directly with a new CREATE MACRO statement instead of PL/SQL functions that return SQL strings. The new syntax supports scalar expressions, FROM-clause table macros, and multiple parameters, while invocation of existing macros remains unchanged.

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. https://dev.to/vahidusefzadeh/sql-macros-in-oracle-database-21c-1igo 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 https://dev.to/vahidusefzadeh/sql-macros-in-oracle-database-21c-1igo 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.