{"id":1158,"date":"2020-02-28T18:20:20","date_gmt":"2020-02-28T16:20:20","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=1158"},"modified":"2020-03-22T18:38:31","modified_gmt":"2020-03-22T16:38:31","slug":"sql-macros-part-2-parameters-and-parsing","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/sql-macros-part-2-parameters-and-parsing\/","title":{"rendered":"SQL Macros Part 2 &#8211; Passing of Parameters and Parsing"},"content":{"rendered":"\n<p>In this part we will keep focus on scalar macros going into more detail and look at parameter passing and parsing. <\/p>\n\n\n\n<!--more-->\n\n\n\n<h4 class=\"wp-block-heading\">The scope of the injected code<\/h4>\n\n\n\n<p>In the <a href=\"https:\/\/blog.sqlora.com\/en\/oracle-20c-sql-macros\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"first part (opens in a new tab)\">first part<\/a> we  created a very simple SQL macro which returns an expression to calculate the employment duration in years. Let&#8217;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? <\/p>\n\n\n\n<p>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?<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; CREATE OR REPLACE FUNCTION job_duration RETURN VARCHAR2 \nSQL_MACRO(SCALAR) AS\nBEGIN\n   RETURN q&#039;! FLOOR(MONTHS_BETWEEN (SYSDATE, hiredate)\/12) AS YEARS,\n            FLOOR(MOD(MONTHS_BETWEEN (SYSDATE, hiredate),12)) AS MONTHS !&#039;;\nEND;\nFunction created.\n&gt;&gt; SELECT ename\n,      job_duration \nFROM   emp e\n  *\nError at line 0\nORA-64626: invalid SQL text returned from SQL macro: \nORA-00907: missing right parenthesis\n<\/pre><\/div>\n\n\n<p><em><strong>Listing 1:<\/strong><\/em> <em><strong>putting more than one expression in a macro doesn&#8217;t work<\/strong><\/em><\/p>\n\n\n\n<p><strong>NO!<\/strong> We cannot &#8220;break&#8221; the structure of the &#8220;parent&#8221; 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 &#8220;normal&#8221; PL\/SQL function returning the the actual value instead of an expression.<\/p>\n\n\n\n<p>In our case we just have to define another macro for the second column as in Listing 2.  Also, don&#8217;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.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [10,23,33,34]; title: ; notranslate\" title=\"\">\nSQL&gt; CREATE OR REPLACE FUNCTION job_duration_years RETURN VARCHAR2 \nSQL_MACRO(SCALAR) AS\nBEGIN\n   RETURN q&#039;! FLOOR(MONTHS_BETWEEN (SYSDATE, hiredate)\/12) !&#039;;\nEND;\nFunction created.\nSQL&gt; CREATE OR REPLACE FUNCTION job_duration_months_remainder RETURN VARCHAR2 \nSQL_MACRO(SCALAR) AS\nBEGIN\n   RETURN q&#039;! FLOOR(MOD(MONTHS_BETWEEN (SYSDATE, hiredate),12)) AS MONTHS !&#039;;\nEND;\nFunction created.\nSQL&gt; SELECT ename\n,      job_duration_years\n,      job_duration_months_remainder\nFROM   emp e\n&gt;&gt; SELECT ename\n,      job_duration_years\n,      job_duration_months_remainder\nFROM   emp e\n  *\nError at line 17\nORA-64626: invalid SQL text returned from SQL macro: \nORA-00907: missing right parenthesis\n\nSQL&gt; CREATE OR REPLACE FUNCTION job_duration_months_remainder RETURN VARCHAR2 \nSQL_MACRO(SCALAR) AS\nBEGIN\n   RETURN q&#039;! FLOOR(MOD(MONTHS_BETWEEN (SYSDATE, hiredate),12)) !&#039;;\nEND;\nFunction created.\nSQL&gt; SELECT ename\n,      job_duration_years as years\n,      job_duration_months_remainder as months\nFROM   emp e\nWHERE  job_duration_years &gt; 38\n\nENAME           YEARS     MONTHS\n---------- ---------- ----------\nSMITH              39          2\nALLEN              39          0\nWARD               39          0\n\n3 rows selected.\n<\/pre><\/div>\n\n\n<p><em><strong>Listing 2: second macro function for second column<\/strong><\/em><\/p>\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-2925154690547867\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-client=\"ca-pub-2925154690547867\" \ndata-ad-slot=\"2727403138\" \ndata-ad-layout=\"in-article\"\ndata-ad-format=\"fluid\"><\/ins>\n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n\n\n\n<h4 class=\"wp-block-heading\">Parameter<\/h4>\n\n\n\n<p>Our macros had no parameters so far. We just hard coded the column HIREDATE. Not very flexible, isn&#8217;t it? For example we could use the same macro to calculate another duration in years using some other columns. So let&#8217;s pass a column as parameter. <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE OR REPLACE FUNCTION job_duration_years (p_hiredate IN DATE) \nRETURN VARCHAR2 SQL_MACRO(SCALAR) AS\nBEGIN\n   RETURN q&#039;! FLOOR(MONTHS_BETWEEN (SYSDATE, p_hiredate)\/12) !&#039;;\nEND;\n\/\n<\/pre><\/div>\n\n\n<p><em><strong>Listing 3: Introducing a parmeter<\/strong><\/em><\/p>\n\n\n\n<p>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&#8230; I also had a strong initial feeling of having to concatenate them or use bind variables. But that&#8217;s wrong! SQL macro functions are special in this aspect. Oracle let the magic happen in the background.<\/p>\n\n\n\n<p>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:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nexec dbms_output.put_line(job_duration_years (DATE &#039;2020-02-26&#039;) ); \nPL\/SQL procedure successfully completed.\nFLOOR(MONTHS_BETWEEN (SYSDATE, p_hiredate)\/12)\n\nexec dbms_output.put_line(job_duration_years (sysdate) ); \nPL\/SQL procedure successfully completed.\nFLOOR(MONTHS_BETWEEN (SYSDATE, p_hiredate)\/12)\n<\/pre><\/div>\n\n\n<p><em><strong>Listing 4: Parameter substitution doesn&#8217;t work outside of a SQL context<\/strong><\/em><\/p>\n\n\n\n<p>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&#8217;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 <strong>column name!<\/strong> And that column name is substituted for the parameter name in the output string of the function!<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [34]; title: ; notranslate\" title=\"\">\nSQL&gt; SELECT ename\n,      job_duration_years(hiredate) as years\nFROM   emp e\nWHERE  job_duration_years(hiredate) &gt; 38\n\nENAME           YEARS\n---------- ----------\nSMITH              39\nALLEN              39\nWARD               39\n\n3 rows selected.\nSQL&gt; select * from dbms_xplan.display_cursor()\n\nPLAN_TABLE_OUTPUT                                                               \n--------------------------------------------------------------------------------\nSQL_ID  c7j36u2v7hfag, child number 1                                           \n-------------------------------------                                           \nSELECT ename ,      job_duration_years(hiredate) as years FROM   emp e          \nWHERE  job_duration_years(hiredate) &gt; 38                                        \n                                                                                \nPlan hash value: 3956160932                                                     \n                                                                                \n--------------------------------------------------------------------------      \n| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |      \n--------------------------------------------------------------------------      \n|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |      \n|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    14 |     3   (0)| 00:00:01 |      \n--------------------------------------------------------------------------      \n                                                                                \nPredicate Information (identified by operation id):                             \n---------------------------------------------------                             \n                                                                                \n   1 - filter(FLOOR(MONTHS_BETWEEN(SYSDATE@!,INTERNAL_FUNCTION(&quot;HIREDATE        \n              &quot;))\/12)&gt;38) \n<\/pre><\/div>\n\n\n<p><em><strong>Listing 5: passing a column as parameter<\/strong><\/em><\/p>\n\n\n\n<p>What happens if we use literals, binds, functions or even subqueries? Let&#8217;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. <\/p>\n\n\n\n<p>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).  <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [37]; title: ; notranslate\" title=\"\">\nSQL&gt; CREATE OR REPLACE FUNCTION new_func (p_param IN VARCHAR2) \nRETURN VARCHAR2 SQL_MACRO(SCALAR) AS\nBEGIN\n   RETURN q&#039;! UPPER(p_param) !&#039;;\nEND;\nFunction created.\nSQL&gt; SELECT \/* TESTSQLMACRO*\/ count(*)\nFROM   emp e\nWHERE  new_func(ename) = &#039;SCOTT&#039;\n\n  COUNT(*)\n----------\n         1\n1 row selected.\nSQL&gt; select * from dbms_xplan.display_cursor()\n\nPLAN_TABLE_OUTPUT                                                               \n--------------------------------------------------------------------------------\nSQL_ID  7nqqpx08h42tq, child number 1                                           \n-------------------------------------                                           \nSELECT \/* TESTSQLMACRO*\/ count(*) FROM   emp e WHERE  new_func(ename) =         \n&#039;SCOTT&#039;                                                                         \n                                                                                \nPlan hash value: 2083865914                                                     \n                                                                                \n---------------------------------------------------------------------------     \n| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |     \n---------------------------------------------------------------------------     \n|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |     \n|   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |     \n|*  2 |   TABLE ACCESS FULL| EMP  |     1 |     6 |     3   (0)| 00:00:01 |     \n---------------------------------------------------------------------------     \n                                                                                \nPredicate Information (identified by operation id):                             \n---------------------------------------------------                             \n                                                                                \n   2 - filter(UPPER(&quot;ENAME&quot;)=&#039;SCOTT&#039;)  \n<\/pre><\/div>\n\n\n<p><em><strong>Listing 6: passing a column as parameter<\/strong><\/em><\/p>\n\n\n\n<p>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(&#8216;ADAMS&#8217;)=&#8217;SCOTT&#8217; and does actually nothing: FILTER (NULL IS NOT NULL) (line 32)<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [32]; title: ; notranslate\" title=\"\">\nSQL&gt; SELECT \/* TESTSQLMACRO*\/ count(*)\nFROM   emp e\nWHERE  new_func(&#039;ADAMS&#039;) = &#039;SCOTT&#039;\n\n  COUNT(*)\n----------\n         0\n1 row selected.\nSQL&gt; select * from dbms_xplan.display_cursor()\n\nPLAN_TABLE_OUTPUT                                                               \n--------------------------------------------------------------------------------\nSQL_ID  4aqcur8g6pjdd, child number 1                                           \n-------------------------------------                                           \nSELECT \/* TESTSQLMACRO*\/ count(*) FROM   emp e WHERE  new_func(&#039;ADAMS&#039;)         \n= &#039;SCOTT&#039;                                                                       \n                                                                                \nPlan hash value: 26940448                                                       \n                                                                                \n--------------------------------------------------------------------            \n| Id  | Operation         | Name   | Rows  | Cost (%CPU)| Time     |            \n--------------------------------------------------------------------            \n|   0 | SELECT STATEMENT  |        |       |     1 (100)|          |            \n|   1 |  SORT AGGREGATE   |        |     1 |            |          |            \n|*  2 |   FILTER          |        |       |            |          |            \n|   3 |    INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |            \n--------------------------------------------------------------------            \n                                                                                \nPredicate Information (identified by operation id):                             \n---------------------------------------------------                             \n                                                                                \n   2 - filter(NULL IS NOT NULL)     \n<\/pre><\/div>\n\n\n<p><em><strong>Listing 7: passing a literal as a  parameter<\/strong><\/em><\/p>\n\n\n\n<p>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.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [27,33]; title: ; notranslate\" title=\"\">\nSQL&gt; SELECT \/* TESTSQLMACRO*\/ count(*)\nFROM   emp e\nWHERE  new_func((SELECT &#039;SCOTT&#039; FROM DUAL)) = &#039;SCOTT&#039;\n\n  COUNT(*)\n----------\n        14\n1 row selected.\nSQL&gt; select * from dbms_xplan.display_cursor()\n\nPLAN_TABLE_OUTPUT                                                               \n--------------------------------------------------------------------------------\nSQL_ID  a451ryvj3w7wk, child number 1                                           \n-------------------------------------                                           \nSELECT \/* TESTSQLMACRO*\/ count(*) FROM   emp e WHERE  new_func((SELECT          \n&#039;SCOTT&#039; FROM DUAL)) = &#039;SCOTT&#039;                                                   \n                                                                                \nPlan hash value: 3655349911                                                     \n                                                                                \n--------------------------------------------------------------------            \n| Id  | Operation         | Name   | Rows  | Cost (%CPU)| Time     |            \n--------------------------------------------------------------------            \n|   0 | SELECT STATEMENT  |        |       |     3 (100)|          |            \n|   1 |  SORT AGGREGATE   |        |     1 |            |          |            \n|*  2 |   FILTER          |        |       |            |          |            \n|   3 |    INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |            \n|   4 |    FAST DUAL      |        |     1 |     2   (0)| 00:00:01 |            \n--------------------------------------------------------------------            \n                                                                                \nPredicate Information (identified by operation id):                             \n---------------------------------------------------                             \n                                                                                \n   2 - filter(UPPER()=&#039;SCOTT&#039;)   \n<\/pre><\/div>\n\n\n<p><em><strong>Listing 8: passing a subquery column as parameter<\/strong><\/em><\/p>\n\n\n\n<p>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&#8217;s good.  <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [32]; title: ; notranslate\" title=\"\">\nSQL&gt; SELECT \/* TESTSQLMACRO*\/ count(*)\nFROM   emp e\nWHERE  new_func(:bind) = &#039;SCOTT&#039;\n\n  COUNT(*)\n----------\n         0\n1 row selected.\nSQL&gt; select * from dbms_xplan.display_cursor()\n\nPLAN_TABLE_OUTPUT                                                               \n--------------------------------------------------------------------------------\nSQL_ID  222mw1zs83m7v, child number 0                                           \n-------------------------------------                                           \nSELECT \/* TESTSQLMACRO*\/ count(*) FROM   emp e WHERE  new_func(:bind) =         \n&#039;SCOTT&#039;                                                                         \n                                                                                \nPlan hash value: 26940448                                                       \n                                                                                \n--------------------------------------------------------------------            \n| Id  | Operation         | Name   | Rows  | Cost (%CPU)| Time     |            \n--------------------------------------------------------------------            \n|   0 | SELECT STATEMENT  |        |       |     1 (100)|          |            \n|   1 |  SORT AGGREGATE   |        |     1 |            |          |            \n|*  2 |   FILTER          |        |       |            |          |            \n|   3 |    INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |            \n--------------------------------------------------------------------            \n                                                                                \nPredicate Information (identified by operation id):                             \n---------------------------------------------------                             \n                                                                                \n   2 - filter(UPPER(:BIND)=&#039;SCOTT&#039;)  \n<\/pre><\/div>\n\n\n<p><em><strong>Listing 9: passing a bind variable as parameter<\/strong><\/em><\/p>\n\n\n\n<p>To summarize<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>don&#8217;t concatenate function parameter into the result string like <br><code><strong>RETURN 'UPPER('||p_param||')'<\/strong><\/code><br>or you&#8217;ll get ORA-64626: invalid SQL text returned from SQL macro: ORA-00909: invalid number of arguments<\/li><li>don&#8217;t use parameter as bind variable in a result string like <br> <code><strong>RETURN 'UPPER(:p_param)'<\/strong><\/code><br>or you&#8217;ll get ORA-64625: bind variables not allowed in the string returned from SQL macro <\/li><li>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<br> <code><strong>RETURN 'UPPER(new_func.p_param)'<\/strong><\/code> <\/li><li>this reference will be replaced with column name, bind variable, function or literal, whatever was used to invoke a sql macro function<\/li><\/ul>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Parsing and deterministic behavior <\/h4>\n\n\n\n<p>A few days ago there was a discussion on Twitter about how SQL macros might influence a parsing:<\/p>\n\n\n\n<figure class=\"wp-block-embed-twitter wp-block-embed is-type-rich is-provider-twitter\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"twitter-tweet\" data-width=\"550\" data-dnt=\"true\"><p lang=\"en\" dir=\"ltr\">and what do you think the documentation means when it says &quot;a SQL macro is always implicitly deterministic&quot;?<\/p>&mdash; Stew Ashton (@StewAshton) <a href=\"https:\/\/twitter.com\/StewAshton\/status\/1232219943661449216?ref_src=twsrc%5Etfw\">February 25, 2020<\/a><\/blockquote><script async src=\"https:\/\/platform.twitter.com\/widgets.js\" charset=\"utf-8\"><\/script>\n<\/div><\/figure>\n\n\n\n<p>Let&#8217;s play a little with our function NEW_FUNC to show the behavior. The documentation says <em>&#8220;a SQL macro is always implicitly deterministic.&#8221; <\/em>   I guess, it means, it should be deterministic and you are responsible for it. Let&#8217;s make our function not deterministic and see what happens. I&#8217;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&#8217;ve placed some <code><strong>DBMS_SESSION.SLEEP<\/strong><\/code> calls, so that we could actually expect different  seconds. But nothing changes because the statement  will not be re-parsed.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; CREATE OR REPLACE FUNCTION new_func (p_param IN VARCHAR2) \nRETURN VARCHAR2 SQL_MACRO(SCALAR) AS\nBEGIN\n   RETURN q&#039;! UPPER(p_param)|| !&#039;||to_char(sysdate,&#039;SS&#039;);\nEND;\nFunction created.\nSQL&gt; -- 1st execution\nSQL&gt; SELECT \/* TEST_PARSING *\/new_func(ename) as ename\nFROM   emp e\nWHERE  ename = &#039;SCOTT&#039;\n\nENAME       \n------------\nSCOTT48     \n1 row selected.\nSQL&gt; SELECT sql_id, child_number, invalidations, executions, plan_hash_value \nFROM   v$sql \nWHERE  sql_text like &#039;%TEST_PARSING%&#039;\nand    sql_text not like &#039;%v$sqlstats%&#039;\n\nSQL_ID        CHILD_NUMBER INVALIDATIONS EXECUTIONS PLAN_HASH_VALUE\n------------- ------------ ------------- ---------- ---------------\nf3bk0fatdhtfg            1             0          1      3956160932\n1 row selected.\nSQL&gt; exec dbms_session.sleep(1)\n PL\/SQL procedure successfully completed.\nSQL&gt; -- 2nd execution\nSQL&gt; set echo off\n\nENAME       \n------------\nSCOTT48     \n1 row selected.\n\nSQL_ID        CHILD_NUMBER INVALIDATIONS EXECUTIONS PLAN_HASH_VALUE\n------------- ------------ ------------- ---------- ---------------\nf3bk0fatdhtfg            1             0          2      3956160932\n1 row selected.\n PL\/SQL procedure successfully completed.\nSQL&gt; -- 3rd execution\nSQL&gt; set echo off\n\nENAME       \n------------\nSCOTT48     \n1 row selected.\n\nSQL_ID        CHILD_NUMBER INVALIDATIONS EXECUTIONS PLAN_HASH_VALUE\n------------- ------------ ------------- ---------- ---------------\nf3bk0fatdhtfg            1             0          3      3956160932\n1 row selected.\n\n<\/pre><\/div>\n\n\n<p><em><strong>Listing 10: non-deterministic SQL macro<\/strong><\/em><\/p>\n\n\n\n<p>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. <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; -- invalidation caused by grant\nSQL&gt; GRANT SELECT ON EMP TO PUBLIC\nGrant complete.\nSQL&gt; -- 4th execution\nSQL&gt; set echo off\n\nENAME       \n------------\nSCOTT52     \n1 row selected.\n\nSQL_ID        CHILD_NUMBER INVALIDATIONS EXECUTIONS PLAN_HASH_VALUE\n------------- ------------ ------------- ---------- ---------------\nf3bk0fatdhtfg            1             1          1      3956160932\n1 row selected.\n\n<\/pre><\/div>\n\n\n<p><em><strong>Listing 11: cursor invalidation<\/strong><\/em><\/p>\n\n\n\n<p>If we change the function itself, we can again see an invalidation and changed output (Listing 12).  <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; -- Function changed\nSQL&gt; CREATE OR REPLACE FUNCTION new_func (p_param IN VARCHAR2) \nRETURN VARCHAR2 SQL_MACRO(SCALAR) AS\nBEGIN\n   RETURN q&#039;! UPPER(p_param)|| !&#039;||to_char(sysdate,&#039;MISS&#039;);\nEND;\nFunction created.\nSQL&gt; set echo off\n\nENAME         \n--------------\nSCOTT2952     \n1 row selected.\n\nSQL_ID        CHILD_NUMBER INVALIDATIONS EXECUTIONS PLAN_HASH_VALUE\n------------- ------------ ------------- ---------- ---------------\nf3bk0fatdhtfg            1             2          1      3956160932\n1 row selected.\n\n<\/pre><\/div>\n\n\n<p><em><strong>Listing 12: invalidation caused by recreate of a function<\/strong><\/em><\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>All SQL statements share the same SQL_ID and have only one child cursor. The SQL text doesn&#8217;t reflect any expressions returned by SQM, so even after changing the function we still have the same cursor.<\/p>\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-2925154690547867\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-client=\"ca-pub-2925154690547867\" \ndata-ad-slot=\"2727403138\" \ndata-ad-layout=\"in-article\"\ndata-ad-format=\"fluid\"><\/ins>\n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n\n\n<div class=\"crp-list-container\"><h3 class=\"crp-list-title\">Related Posts<\/h3><ul class=\"crp-list\"><li class=\"crp-list-item crp-list-item-image-none\"><div class=\"crp-list-item-title\"><a href=\"https:\/\/blog.sqlora.com\/en\/oracle-20c-sql-macros\/\" target=\"_blank\">Oracle 20c: SQL Macros<\/a><\/div><\/li><li class=\"crp-list-item crp-list-item-image-none\"><div class=\"crp-list-item-title\"><a href=\"https:\/\/blog.sqlora.com\/en\/building-hash-keys-using-sql-macros-in-oracle-20c\/\" target=\"_blank\">Building Hash Keys using SQL Macros in Oracle 20c<\/a><\/div><\/li><li class=\"crp-list-item crp-list-item-image-none\"><div class=\"crp-list-item-title\"><a href=\"https:\/\/blog.sqlora.com\/en\/parameterized-views-in-oracle-no-problem-with-sql-macros\/\" target=\"_blank\">Parameterized Views in Oracle? No problem! With SQL macros!<\/a><\/div><\/li><li class=\"crp-list-item crp-list-item-image-none\"><div class=\"crp-list-item-title\"><a href=\"https:\/\/blog.sqlora.com\/en\/temporal-joins-with-sql-macros-in-oracle-20c\/\" target=\"_blank\">Temporal Joins with SQL Macros in Oracle 20c<\/a><\/div><\/li><\/ul><\/div>","protected":false},"excerpt":{"rendered":"<p>In this part we will keep focus on scalar macros going into more detail and look at parameter passing and parsing.<\/p>\n","protected":false},"author":1,"featured_media":1201,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[99,2,3,100],"tags":[102,103],"class_list":["post-1158","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-20c","category-oracle","category-sql","category-sql-macros","tag-20c","tag-sql-macros"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1158","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/comments?post=1158"}],"version-history":[{"count":50,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1158\/revisions"}],"predecessor-version":[{"id":1581,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1158\/revisions\/1581"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media\/1201"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media?parent=1158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/categories?post=1158"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/tags?post=1158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}