In this part we will keep focus on scalar macros going into more detail and look at parameter passing and parsing.
The scope of the injected code
In the first part we created a very simple SQL macro which returns an expression to calculate the employment duration in years. Let’s try to extend it. We only get full years with out macro. What if we wanted to see years and months in two separate columns?
We have learned that SQL macros allow us to inject a piece of SQL code at the position where the function is called. Can we inject the whole blocks like that?
SQL> CREATE OR REPLACE FUNCTION job_duration RETURN VARCHAR2
SQL_MACRO(SCALAR) AS
BEGIN
RETURN q'! FLOOR(MONTHS_BETWEEN (SYSDATE, hiredate)/12) AS YEARS,
FLOOR(MOD(MONTHS_BETWEEN (SYSDATE, hiredate),12)) AS MONTHS !';
END;
Function created.
>> SELECT ename
, job_duration
FROM emp e
*
Error at line 0
ORA-64626: invalid SQL text returned from SQL macro:
ORA-00907: missing right parenthesis
Listing 1: putting more than one expression in a macro doesn’t work
NO! We cannot “break” the structure of the “parent” SQL statement. If we use the function in SELECT list, then it is expected to return an expression only for one result column, exactly as it would be for “normal” PL/SQL function returning the the actual value instead of an expression.
In our case we just have to define another macro for the second column as in Listing 2. Also, don’t try to put column aliases into the expression returned from SQM function, like on line 10. This again raises an error (line 23). Rather, define aliases in the query itself (line 33,34) This makes sense! We are using the same macro in the WHERE clause too, where aliases returned from the function are just out of place.
SQL> CREATE OR REPLACE FUNCTION job_duration_years RETURN VARCHAR2
SQL_MACRO(SCALAR) AS
BEGIN
RETURN q'! FLOOR(MONTHS_BETWEEN (SYSDATE, hiredate)/12) !';
END;
Function created.
SQL> CREATE OR REPLACE FUNCTION job_duration_months_remainder RETURN VARCHAR2
SQL_MACRO(SCALAR) AS
BEGIN
RETURN q'! FLOOR(MOD(MONTHS_BETWEEN (SYSDATE, hiredate),12)) AS MONTHS !';
END;
Function created.
SQL> SELECT ename
, job_duration_years
, job_duration_months_remainder
FROM emp e
>> SELECT ename
, job_duration_years
, job_duration_months_remainder
FROM emp e
*
Error at line 17
ORA-64626: invalid SQL text returned from SQL macro:
ORA-00907: missing right parenthesis
SQL> CREATE OR REPLACE FUNCTION job_duration_months_remainder RETURN VARCHAR2
SQL_MACRO(SCALAR) AS
BEGIN
RETURN q'! FLOOR(MOD(MONTHS_BETWEEN (SYSDATE, hiredate),12)) !';
END;
Function created.
SQL> SELECT ename
, job_duration_years as years
, job_duration_months_remainder as months
FROM emp e
WHERE job_duration_years > 38
ENAME YEARS MONTHS
---------- ---------- ----------
SMITH 39 2
ALLEN 39 0
WARD 39 0
3 rows selected.
Listing 2: second macro function for second column
Parameter
Our macros had no parameters so far. We just hard coded the column HIREDATE. Not very flexible, isn’t it? For example we could use the same macro to calculate another duration in years using some other columns. So let’s pass a column as parameter.
CREATE OR REPLACE FUNCTION job_duration_years (p_hiredate IN DATE)
RETURN VARCHAR2 SQL_MACRO(SCALAR) AS
BEGIN
RETURN q'! FLOOR(MONTHS_BETWEEN (SYSDATE, p_hiredate)/12) !';
END;
/
Listing 3: Introducing a parmeter
The way we do it is some kind unusual for me. Parameters are referenced inside of the quoted string. How can they actually be useful in that case, you may ask. Just literal strings… I also had a strong initial feeling of having to concatenate them or use bind variables. But that’s wrong! SQL macro functions are special in this aspect. Oracle let the magic happen in the background.
We can pass column names, literals or bind variables as parameter. First, we test the function as standalone, that means, not embedded in a SQL statement:
exec dbms_output.put_line(job_duration_years (DATE '2020-02-26') );
PL/SQL procedure successfully completed.
FLOOR(MONTHS_BETWEEN (SYSDATE, p_hiredate)/12)
exec dbms_output.put_line(job_duration_years (sysdate) );
PL/SQL procedure successfully completed.
FLOOR(MONTHS_BETWEEN (SYSDATE, p_hiredate)/12)
Listing 4: Parameter substitution doesn’t work outside of a SQL context
It actually works as you would normally expect: no matter what we pass, the return string is not changed in any way. But if called in SQL (Listing 5), it behaves just different. Let’s pass the column HIREDATE. What does it mean, if we pass a column? What will be passed into the function? The statement is not executed yet, so it will not be the the actual data in that column. As we can see in the filter operation of the execution plan (line 34), what is actually passed is the column name! And that column name is substituted for the parameter name in the output string of the function!
SQL> SELECT ename
, job_duration_years(hiredate) as years
FROM emp e
WHERE job_duration_years(hiredate) > 38
ENAME YEARS
---------- ----------
SMITH 39
ALLEN 39
WARD 39
3 rows selected.
SQL> select * from dbms_xplan.display_cursor()
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID c7j36u2v7hfag, child number 1
-------------------------------------
SELECT ename , job_duration_years(hiredate) as years FROM emp e
WHERE job_duration_years(hiredate) > 38
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(FLOOR(MONTHS_BETWEEN(SYSDATE@!,INTERNAL_FUNCTION("HIREDATE
"))/12)>38)
Listing 5: passing a column as parameter
What happens if we use literals, binds, functions or even subqueries? Let’s test it with slightly different example. We introduce the new macro function NEW_FUNC that just turns the parameter to uppercase and we will use this macro in the WHERE clause.
In the first testcase (Listing 6) we use the column name as a parameter again and we can clearly see that name in the filter condition (line 37).
SQL> CREATE OR REPLACE FUNCTION new_func (p_param IN VARCHAR2)
RETURN VARCHAR2 SQL_MACRO(SCALAR) AS
BEGIN
RETURN q'! UPPER(p_param) !';
END;
Function created.
SQL> SELECT /* TESTSQLMACRO*/ count(*)
FROM emp e
WHERE new_func(ename) = 'SCOTT'
COUNT(*)
----------
1
1 row selected.
SQL> select * from dbms_xplan.display_cursor()
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7nqqpx08h42tq, child number 1
-------------------------------------
SELECT /* TESTSQLMACRO*/ count(*) FROM emp e WHERE new_func(ename) =
'SCOTT'
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(UPPER("ENAME")='SCOTT')
Listing 6: passing a column as parameter
In case of a literal parameter we see that this literal is passed and immediately substituted into the return string so that the optimizer was able to figure out the false condition UPPER(‘ADAMS’)=’SCOTT’ and does actually nothing: FILTER (NULL IS NOT NULL) (line 32)
SQL> SELECT /* TESTSQLMACRO*/ count(*)
FROM emp e
WHERE new_func('ADAMS') = 'SCOTT'
COUNT(*)
----------
0
1 row selected.
SQL> select * from dbms_xplan.display_cursor()
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 4aqcur8g6pjdd, child number 1
-------------------------------------
SELECT /* TESTSQLMACRO*/ count(*) FROM emp e WHERE new_func('ADAMS')
= 'SCOTT'
Plan hash value: 26940448
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
|* 2 | FILTER | | | | |
| 3 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NULL IS NOT NULL)
Listing 7: passing a literal as a parameter
Using a subquery is not shown correctly by explain plan for the filter condition (Listing 8, line 33), but you can see it as operation 4 of the execution plan (line 27). The subquery would also be substituted in a macro result and not evaluated at parse time.
SQL> SELECT /* TESTSQLMACRO*/ count(*)
FROM emp e
WHERE new_func((SELECT 'SCOTT' FROM DUAL)) = 'SCOTT'
COUNT(*)
----------
14
1 row selected.
SQL> select * from dbms_xplan.display_cursor()
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID a451ryvj3w7wk, child number 1
-------------------------------------
SELECT /* TESTSQLMACRO*/ count(*) FROM emp e WHERE new_func((SELECT
'SCOTT' FROM DUAL)) = 'SCOTT'
Plan hash value: 3655349911
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
|* 2 | FILTER | | | | |
| 3 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
| 4 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(UPPER()='SCOTT')
Listing 8: passing a subquery column as parameter
The last one is using bind variables (Listing 9). Again, we can see that the parameter in the return string is substituted by the bind variable. So if you are using binds as parameter for SQM, you will also get binds in your result SQL and that’s good.
SQL> SELECT /* TESTSQLMACRO*/ count(*)
FROM emp e
WHERE new_func(:bind) = 'SCOTT'
COUNT(*)
----------
0
1 row selected.
SQL> select * from dbms_xplan.display_cursor()
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 222mw1zs83m7v, child number 0
-------------------------------------
SELECT /* TESTSQLMACRO*/ count(*) FROM emp e WHERE new_func(:bind) =
'SCOTT'
Plan hash value: 26940448
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
|* 2 | FILTER | | | | |
| 3 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(UPPER(:BIND)='SCOTT')
Listing 9: passing a bind variable as parameter
To summarize
- don’t concatenate function parameter into the result string like
RETURN 'UPPER('||p_param||')'
or you’ll get ORA-64626: invalid SQL text returned from SQL macro: ORA-00909: invalid number of arguments - don’t use parameter as bind variable in a result string like
RETURN 'UPPER(:p_param)'
or you’ll get ORA-64625: bind variables not allowed in the string returned from SQL macro - just reference the parameter in a literal string to be returned, you can optionally prefix it with a function name if conflicting with other names
RETURN 'UPPER(new_func.p_param)' - this reference will be replaced with column name, bind variable, function or literal, whatever was used to invoke a sql macro function
Parsing and deterministic behavior
A few days ago there was a discussion on Twitter about how SQL macros might influence a parsing:
Let’s play a little with our function NEW_FUNC to show the behavior. The documentation says “a SQL macro is always implicitly deterministic.” I guess, it means, it should be deterministic and you are responsible for it. Let’s make our function not deterministic and see what happens. I’ll add a seconds part of sysdate at the end of the expression returned from SQM (Listing 10). Let execute the SQL 3 times. I’ve placed some DBMS_SESSION.SLEEP calls, so that we could actually expect different seconds. But nothing changes because the statement will not be re-parsed.
SQL> CREATE OR REPLACE FUNCTION new_func (p_param IN VARCHAR2)
RETURN VARCHAR2 SQL_MACRO(SCALAR) AS
BEGIN
RETURN q'! UPPER(p_param)|| !'||to_char(sysdate,'SS');
END;
Function created.
SQL> -- 1st execution
SQL> SELECT /* TEST_PARSING */new_func(ename) as ename
FROM emp e
WHERE ename = 'SCOTT'
ENAME
------------
SCOTT48
1 row selected.
SQL> SELECT sql_id, child_number, invalidations, executions, plan_hash_value
FROM v$sql
WHERE sql_text like '%TEST_PARSING%'
and sql_text not like '%v$sqlstats%'
SQL_ID CHILD_NUMBER INVALIDATIONS EXECUTIONS PLAN_HASH_VALUE
------------- ------------ ------------- ---------- ---------------
f3bk0fatdhtfg 1 0 1 3956160932
1 row selected.
SQL> exec dbms_session.sleep(1)
PL/SQL procedure successfully completed.
SQL> -- 2nd execution
SQL> set echo off
ENAME
------------
SCOTT48
1 row selected.
SQL_ID CHILD_NUMBER INVALIDATIONS EXECUTIONS PLAN_HASH_VALUE
------------- ------------ ------------- ---------- ---------------
f3bk0fatdhtfg 1 0 2 3956160932
1 row selected.
PL/SQL procedure successfully completed.
SQL> -- 3rd execution
SQL> set echo off
ENAME
------------
SCOTT48
1 row selected.
SQL_ID CHILD_NUMBER INVALIDATIONS EXECUTIONS PLAN_HASH_VALUE
------------- ------------ ------------- ---------- ---------------
f3bk0fatdhtfg 1 0 3 3956160932
1 row selected.
Listing 10: non-deterministic SQL macro
Now we can invalidate the cursor by issuing a DDL command, for example granting a SELECT privilege. The cursor has been invalidated and re-parsed and now we can see different output.
SQL> -- invalidation caused by grant
SQL> GRANT SELECT ON EMP TO PUBLIC
Grant complete.
SQL> -- 4th execution
SQL> set echo off
ENAME
------------
SCOTT52
1 row selected.
SQL_ID CHILD_NUMBER INVALIDATIONS EXECUTIONS PLAN_HASH_VALUE
------------- ------------ ------------- ---------- ---------------
f3bk0fatdhtfg 1 1 1 3956160932
1 row selected.
Listing 11: cursor invalidation
If we change the function itself, we can again see an invalidation and changed output (Listing 12).
SQL> -- Function changed
SQL> CREATE OR REPLACE FUNCTION new_func (p_param IN VARCHAR2)
RETURN VARCHAR2 SQL_MACRO(SCALAR) AS
BEGIN
RETURN q'! UPPER(p_param)|| !'||to_char(sysdate,'MISS');
END;
Function created.
SQL> set echo off
ENAME
--------------
SCOTT2952
1 row selected.
SQL_ID CHILD_NUMBER INVALIDATIONS EXECUTIONS PLAN_HASH_VALUE
------------- ------------ ------------- ---------- ---------------
f3bk0fatdhtfg 1 2 1 3956160932
1 row selected.
Listing 12: invalidation caused by recreate of a function
It is probably not a good idea to you make the SQM function non-deterministic, e.g select something from the database. Should the output of the function change, it will only have an impact if and when the cursor becomes invalidated and re-parsed.
All SQL statements share the same SQL_ID and have only one child cursor. The SQL text doesn’t reflect any expressions returned by SQM, so even after changing the function we still have the same cursor.