I was waiting for this for a long time. Finally we can use sql macro in Oracle database. Actually I was excited when I first read about this but after my tests, I stuck at some points. I was hoping to use this as “parameterized views” as in, send a parameter and use it to choose which select statement runs and I am kind of able to do that but not I expected. I have read about the documentation but not much detail. I will mentioned about some “odd” staff that I found about sql macro at the end of this post.
And this could help us about the conditions of: “where customer_name = nvl(:Cust_name, customer_name”. I think many of you know this and suffer it right 🙂 if parameter is null then bring all of them but if it is not null then just bring matched rows.
EDIT: bummer! thanks to Mike Cutz from oracle community, showed me a mistake on my performance compare below. while I was testing, I used constant ‘EMPLOYEES’ value instead of bind variable. so I am changing that part.
So, what is sql macro. Sql macro is an SQL add-on to enhance the sql capability. mostly we use PLSQL when we need to do something complex and can not do it just by sql but this cause “context switch”. you can think this as, there are two engines in Oracle, one is SQL and the other PLSQL. two engines does not do other’s job. if a plsql statement requires to run an SQL then statements is sent to SQL engine and if sql engine finds a plsql (a function in select statement for ex) then it is sent to plsql engine to run. going back and forward between these engines is called “context switch” and context switch is a performance ENEMY! So we always try to do our job in just one engine but sometimes we have to use both. This is a new feature for 20c but I am running those code on 19.6.
SQL Macro will reduce the requirement of PLSQL so there won’t be context switch and we will have more performance. Also, it does help about getting better execution plans for complex queries some how. There are 2 types of SQL Macro, Scalar and Table. default is table and I will demonstrate table SM here.
Table SQL Macro works and looks like pipelined functions but remember, this is run by sql engine not by plsql.
I just created a dummy table and created SF_TEST sql macro. sf_test looks like just a regular function with one varchar2 parameter but at the definition we use SQL_MACRO before “as” keyword. and then just return a string which has a select statement. here is usage:
as you see our sql macro worked as a pipelined function and returned object information (TMP table) with named EMPLOYEES. basically it is just running the select statement in the sql macro and this operation is done in sql engine (well at least mostly…).
let’s make another example:
cool right? I can parameterized the number of rows to return from an sql statement.
Let’s take it further:
this is really cool! we can pass a “table” as parameter (which is come with polymorphic table functions actually) to our sql macro and run our query based on this table. in my example I passed 4 (number of rows) and “hr.employees” table. please be careful, hr.employees is not a varchar2 variable! this is not a string concatenation operation. Now we can get any rows from any table:
So, let’s get back to performance enhancement. Actually I am not sure about the exact result but I run a few simple test and it showed, execution plans are more accurate. here is an example:
now, I make my first query a little bit complex with using some NVL functions and bind variables. now I use 4 bind variables (owner, object_name, object_type, object_id) but only OBJECT_NAME has a value and others are null. also please remember I created and index on OBJECT_NAME on TMP table. so in this query I am looking for all objects with name EMPLOYEES but since Oracle needs to check all values if they are null or not, execution plan becomes more complex and cost is higher. the equivalent version of this query (with this bind variable values) is:
Select * from tmp where object_name = ‘EMPLOYEES’;
and this is a very simple query which can use the index on TMP table.
EDIT: I deleted this part and rewriting it please see my previous edit at top.
I will discuss about reading variable values in sql macro after that but as a sneak peek, you can not read any variable value in sql macro except NUMBER types. so to eliminate the problem above (column = nvl(paramater, column)) we can at least do something by adding more number variables:
we can shape our sql in sql macro. as I said since I can only read NUMBER variables, I added parameters that indicates whether the real parameter is null or not and based on this information I constracted my sql statements.
What is “odd” about this?
when I see the first examples, I thought to create condition list using the parameter values. if a parameter is not null then add “column = p_parameter1” to query but something is different in this environment. even if we are writing a “function” and can use plsql commands like “if then else” or for loop, we can not read parameter values (even more oddly) except NUMBERS.
everything is normal here, I am showing the value using dbms_output and also my select is fetching parameter value from dual and both okay. query returned 1 row with value 10 and output is showing correct value.
Let’s do it with a varchar2:
as you see, when parameter type is varchar2, then I can not read it inside the sql macro! my output is null (parameter p_param is) but query returned the value of parameter. same thing happens for date type too. So I can not use “if p_param = ‘ABC’ then” condition because p_param is null inside the sql macro but I can use “if p_param = 1 then” if p_param is a number.
I am running these codes on version 19.6 but also run them livesql.oracle.com which is 19.8 and same result.
I don’t know why it is acting like this maybe a bug maybe a feature 😀 I told that this is run by sql not plsql but I think this is not entirely true. some plsql engine is involved here but parameter values are not passed to plsql engine except numbers. of course this is just a guess I am not sure about it but I would like to use parameter values inside the sql macro.
wish you all healthy, corona free days.