SQL Macros - Pipelined Function killers?

Introduced in Oracle 21c and back-ported to Oracle 19c, SQL Macros have been an excellent addition to the developer's toolset. These "parameterized views" come in two different flavours, Table Expressions and Scalar Expressions, with the latter only available in 21c onwards and they offer many advantages over pipelined functions (and polymorphic table functions).

Firstly, they are somewhat more straightforward to work with from a developer's perspective as you are ultimately just returning a valid SQL string from the function that has the magic SQL_MACRO keyword added to its definition. No need to work with user-defined types to pipe the output through, or work with abstract dbms_tf packaged types (although this can still be leveraged) for polymorphic table functions. Simply generate a valid SQL statement as a string or CLOB and Oracle will run it.

Secondly, they are more efficient as they do not require a context switch from the SQL engine to the PL/SQL engine and back again, they are executed during the parsing step and incorporated directly into the SQL query utilising it. This is where some potential side effects can come into play if you expect them to behave like their predecessors.

Although the SQL Macro is created as a PL/SQL function, any parameters passed in cannot be used outside of the SQL statement that the function generates. Normally if you call a function or procedure that accepts parameters you'd expect to be able to use those parameters within the function or procedure being called but not with SQL Macros you can't.

To demonstrate, I have a pipeline function that I created some time ago that allowed me to output the display and return values from an APEX list of values (LOV) dynamically, catering for any bind variables that may reside within it.

💡

The backstory to this is there were 3 LOV's being used on 3 different pages with near-identical SQL queries, the page items being referenced within each one were the only differentiating factors. If that query needed to change, it had to be changed in 3 places (often overlooked) so making a single generic LOV allowed me to use the pipelined function to pass in the page items as parameters and maintain the query in one place. It isn't used that much to be fair, researching this blog is the most attention it has received in some time!

So I figured this may be a good use case to switch it to a SQL Macro and save on the context switch. One of the first things this function does is look up the LOV's query from the APEX_APPLICATION_LOVS view using the LOV name passed in as a parameter. Then it goes on to use the query string to parse the bind variables supplied as a delimited string but the macro falls before we even get this far.

It cannot see the parameter value for the LOV name (or any others) at parse time when it is generating the SQL statement, understandably, as it's parsing the query not executing it so it is oblivious of the value the parameter holds from the calling query and it is, therefore, always NULL.

💡

Although the optimizer will often perform "bind peeking" when it performs a hard parse of the SQL statement the first time it presents itself, it doesn't do this for all subsequent soft parses and that is why the parameter is always NULL as it cannot guarantee visibility to it.

So, this brought my efforts to an abrupt and early end as I needed to be able to retrieve and manipulate the LOV's base query to build my new SQL string that forms the macro's output. I guess you could use SYS_CONTEXT or a global temporary table (or an APEX collection!) to store the parameters but this defeats the objective of having all the work encapsulated in a single function call and would require some prep work ahead of time.

In conclusion, pipelined functions still have a role to play, albeit now for more of the fringe cases like my example here, but I suspect a great many of them will be replaced over time by SQL Macros and the advantages mentioned in this blog reaped by those who use them.