{"id":1910,"date":"2024-01-03T23:09:33","date_gmt":"2024-01-03T21:09:33","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=1910"},"modified":"2024-09-16T18:11:00","modified_gmt":"2024-09-16T16:11:00","slug":"sql-macros-some-less-obvious-facts-part-1","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/sql-macros-some-less-obvious-facts-part-1\/","title":{"rendered":"SQL Macros &#8211; Some Less Obvious Facts (Part 1)"},"content":{"rendered":"\n<p>Since I have written a lot about SQL macros, I have tested some less obvious, sometimes surprising cases. But expanding about them in the original posts would just overload them, so I decided to make a dedicated post for that. The other good reason for this is that the SQL macros are, in my opinion,  not documented in enough detail. So additional research and testing can be useful.<\/p>\n\n\n\n<!--more-->\n\n\n\n<h4 class=\"wp-block-heading\">TL;DR<\/h4>\n\n\n\n<p>If you don&#8217;t have time to read the whole story, here are the key findings:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SQL macros are allowed where PL\/SQL functions can be called<\/li>\n\n\n\n<li>This means not only in SELECT, WHERE, GROUP BY, HAVING, ORDER BY clauses, but also as a part of a FROM clause in a join condition, MATCH_RECOGNIZE, VALUES clause, etc.<\/li>\n\n\n\n<li>As of now (Oracle 23.3), there is a bug prohibiting the use of scalar macros in GROUP BY clause<\/li>\n\n\n\n<li>SQL macros are also allowed in DML and even some DDL (CTAS)<\/li>\n\n\n\n<li>For other DDL such as maintenance operations with filtering or creation of function-based indexes it is disallowed<\/li>\n\n\n\n<li>Nesting of SQL macros (both scalar and table ones) is not possible at the SQL level<\/li>\n\n\n\n<li>But you can nest them in the macro body (only one at once)<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">Where are the scalar SQL Macros allowed? <\/h4>\n\n\n\n<p>There are several sections in the documentation explaining where the scalar macros can be called. Here are two quotes from the documentation.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>1. SQL macros can either be scalar expressions, typically used in SELECT lists, WHERE, GROUP BY and HAVING clauses, to encapsulate calculations and business logic or can be table expressions, typically used in a FROM clause<\/p>\n\n\n\n<p>2. A&nbsp;<code>SCALAR<\/code>&nbsp;macro cannot appear in&nbsp;<code>FROM<\/code>&nbsp;clause of a query table expression. It can appear wherever PL\/SQL functions are allowed, for example in the select list, the&nbsp;<code>WHERE<\/code>&nbsp;clause, and the&nbsp;<code>ORDER BY<\/code>&nbsp;clause.<\/p>\n<\/blockquote>\n\n\n\n<p>This and also the presence of the error <strong>ORA-64628: scalar SQL macro cannot appear in FROM clause of a SQL statement<\/strong> suggests that the scalar SQL macros are not allowed in the FROM clause. However, this is not entirely correct. I have studied the syntax diagrams in the <a rel=\"noreferrer noopener\" href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/23\/sqlrf\/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__CHDCEJJE\" target=\"_blank\">SQL Language Reference<\/a>, trying to find this thing <strong>&#8220;<code>FROM<\/code>&nbsp;clause of a query table expression&#8221;<\/strong> mentioned in the second quote &#8211; without success. After all, I think it should be the other way round: &#8220;<strong>a query table expression of the FROM clause<\/strong>&#8220;<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2023\/12\/image.png\"><img loading=\"lazy\" decoding=\"async\" width=\"914\" height=\"485\" src=\"https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2023\/12\/image.png\" alt=\"\" class=\"wp-image-2018\" srcset=\"https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2023\/12\/image.png 914w, https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2023\/12\/image-300x159.png 300w, https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2023\/12\/image-768x408.png 768w, https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2023\/12\/image-624x331.png 624w\" sizes=\"auto, (max-width: 914px) 100vw, 914px\" \/><\/a><figcaption class=\"wp-element-caption\">Figure 1: Syntax diagrams for the FROM clause of a query<\/figcaption><\/figure>\n\n\n\n<p>As you can see, the FROM clause consists of, among other things, <strong><em>&#8220;table_reference&#8221;<\/em><\/strong>, which in turn contains <strong><em>&#8220;query_table_expression&#8221;<\/em><\/strong>, but also some other clauses. Only in this <strong><em>&#8220;query_table_expression&#8221;<\/em><\/strong> are the scalar macros disallowed.  If we look at other clauses, we&#8217;ll see that the rule &#8220;<em>it can appear wherever <strong>PL\/SQL<\/strong> functions are allowed<\/em>&#8221; works pretty well!<\/p>\n\n\n\n<p>All following examples do not pretend to make any sense, but rather just to show what is possible.<\/p>\n\n\n\n<p>Here is an example of using scalar SQL macro in the VALUES clause (table value constructor &#8211; 23c)<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [3]; title: ; notranslate\" title=\"\">\nSQL&gt; select *\n  2  from (values (1,&#039;A&#039;)\n  3          ,    (2, upper_macro(&#039;b&#039;))\n  4       ) c (id, name);\n\n        ID N\n---------- -\n         1 A\n         2 B\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 1: SQL macro in values clause<\/em><\/strong><\/p>\n\n\n\n<p>And here follows the example of using it in a MATCH_RECOGNIZE clause.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [5]; title: ; notranslate\" title=\"\">\nSQL&gt; select *\n  2  from   emp match_recognize \n  3      (partition by job \n  4       order by empno \n  5       measures  upper_macro(lower(ename)) as ename \n  6       pattern(a) \n  7       define a as (1=1) );\n\nJOB       ENAME     \n--------- ----------\nANALYST   SCOTT     \nANALYST   FORD      \nCLERK     SMITH     \nCLERK     ADAMS   \n...  \n<\/pre><\/div>\n\n\n<p><strong><em>Listing 2: scalar SQL macro in MATCH_RECOGNIZE<\/em><\/strong><\/p>\n\n\n\n<p>If we look at the PIVOT clause, the PL\/SQL functions are unfortunately not allowed there. The same is valid for UNPIVOT. Therefore no SQL macros either.  <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; select * \n  2  from   t_emp_kv\n  3  pivot (any_value(key_val_char) for key_name IN (upper_macro(&#039;ENAME&#039;),&#039;JOB&#039;));\n\nError report -\nSQL Error: ORA-56901: non-constant expression in pivot or unpivot values clause\n56901. 0000 -  &quot;non-constant expression is not allowed for pivot|unpivot values&quot;\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 3: no scalar macros in the PIVOT clause<\/em><\/strong><\/p>\n\n\n\n<p>Actually, if the SQL macro returns a literal\/constant, then, after macro expansion,  the statement would be just fine. But obviously this check takes place before macro expansion, so we get ORA-56901.<\/p>\n\n\n\n<p>Another sub-clauses of the <strong><em>table_reference<\/em><\/strong> doesn&#8217;t allow for user-defined PL\/SQL functions. But besides <strong><em>table_reference<\/em><\/strong> there are also <strong><em>join_clause<\/em><\/strong> and <strong><em>inline_analytic_view<\/em><\/strong> there. What about them?<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; create or replace function join_col_par (p_col in varchar2)\n  2    return varchar2 sql_macro(scalar) is\n  3  begin\n  4    return &#039;round(join_col_par.p_col,-2)&#039;;\n  5  end;\n  6  \/\n\nFunction JOIN_COL_PAR compiled\n\nSQL&gt; \nSQL&gt; select e1.ename, e1.sal, e2.ename, e2.sal\n  2  from emp e1 join emp e2 \n  3              on e1.sal = join_col_par(e2.sal)\n  4              and e1.empno != e2.empno ;\n\nENAME             SAL ENAME             SAL\n---------- ---------- ---------- ----------\nMILLER           1300 WARD             1250\nSCOTT            3000 JONES            2975\nFORD             3000 JONES            2975\nMILLER           1300 MARTIN           1250\nFORD             3000 SCOTT            3000\nSCOTT            3000 FORD             3000\n\n6 rows selected. \n<\/pre><\/div>\n\n\n<p><strong><em>Listing 4: scalar SQL macros in the JOIN clause<\/em><\/strong><\/p>\n\n\n\n<p>As can be seen in the Listing 4, it works in the JOIN clause. I have not tested the <strong><em>inline_analytic_view<\/em><\/strong> clause and I also miss the new <strong><em>GRAPH_TABLE<\/em><\/strong> operator (23c) on this syntax diagrams, which I leave to you. <\/p>\n\n\n\n<h4 class=\"wp-block-heading\">A note about GROUP BY<\/h4>\n\n\n\n<p>Although using SQL macros in a GROUP BY clause is explicitly mentioned in the documentation, this doesn&#8217;t work as of Oracle 23.3 and turns to be a bug.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-rich is-provider-twitter wp-block-embed-twitter\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"twitter-tweet\" data-width=\"550\" data-dnt=\"true\"><p lang=\"en\" dir=\"ltr\">Thanks for reporting this; it&#39;s a bug<\/p>&mdash; Chris Saxon (@ChrisRSaxon) <a href=\"https:\/\/twitter.com\/ChrisRSaxon\/status\/1721503567582794123?ref_src=twsrc%5Etfw\">November 6, 2023<\/a><\/blockquote><script async src=\"https:\/\/platform.twitter.com\/widgets.js\" charset=\"utf-8\"><\/script>\n<\/div><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">SQL macros in DML and DDL<\/h4>\n\n\n\n<p>In the context of SQL macros, people often only talk about queries. But one can use them in DML statements too:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; update emp \n  2  set ename = upper_macro(ename);\n\n14 rows updated.\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 5: scalar macros can be used in DML<\/em> <\/strong><\/p>\n\n\n\n<p>What about DDL? For the most obvious DDL command as a use case &#8211; <strong>C<\/strong>reate <strong>T<\/strong>able <strong>A<\/strong>s <strong>S<\/strong>elect &#8211; it works.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\ncreate or replace function get_filter(p_column_name in varchar2) \nreturn varchar2 sql_macro(scalar) as\nbegin\n     return &#039;p_column_name&#039;;\nend;\n\/\n\nSQL&gt; create table emp2\n  2  as \n  3  select * from emp\n  4  where get_filter (deptno) &lt;=20;\n\nTable EMP2 created.\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 6: scalar macros work for CTAS<\/em><\/strong><\/p>\n\n\n\n<p>But unfortunately it didn&#8217;t work for the other use cases that came to mind, such as filtered partition\/table maintenance operations or function based index creation:  <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; alter table emp move online\n  2    including rows where get_filter (deptno) &lt;=40;\n\nError report -\nORA-14468: invalid INCLUDING ROWS WHERE ... clause\n14468. 00000 -   &quot;invalid INCLUDING ROWS WHERE ... clause&quot;\n*Document: Yes\n*Cause:    An invalid INCLUDING ROWS WHERE ... clause was specified.\n           The INCLUDING ROWS WHERE ... clause has the following restrictions:\n           - It can only refer to the target table of the DDL.\n           - It cannot have subselect queries.\n           - It cannot refer to the SYSDATE function.\n           - It cannot refer to the ROWNUM pseudo column or the ROWID column.\n*Action:   Rewrite the WHERE clause so that it does not have the restrictions\n           previously listed.\n\nSQL&gt; create index emp_fbi on emp (upper_macro(ename));\n\nError report -\nORA-64632: SQL macro is not supported with Functional Index\n64632. 00000 -  &quot;SQL macro is not supported with %s&quot; \n*Cause:    The SQL macro was not supported with this feature.\n*Action:   Do not use SQL macro with this feature.\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 7: scalar macros don&#8217;t work in DDL<\/em><\/strong><\/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<p><\/p>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Nesting of SQL macros<\/h4>\n\n\n\n<p>The nesting of the SQL macros is not allowed. At least in terms of SQL statement. If we had defined two macros to lower the input and to add spaces after each character (don&#8217;t ask me why I&#8217;m using macros for this &#8211; it&#8217;s just to show, while keeping it simple), combining them in as single SQL statement wouldn&#8217;t work.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; create or replace function lower_macro (p_param in varchar2) \n  2  return varchar2 sql_macro(scalar) as\n  3  begin\n  4     return &#039;lower(p_param)&#039;;\n  5  end;\n  6  \/\n\nFunction LOWER_MACRO compiled\n\nSQL&gt; \nSQL&gt; create or replace function  add_blanks_macro (p_param in varchar2) \n  2  return varchar2 sql_macro(scalar) as\n  3  begin\n  4     return q&#039;&#x5B;regexp_replace(p_param,&#039;(.)&#039;,&#039;\\1 &#039;)]&#039;;\n  5  end;\n  6  \/\n\nFunction ADD_BLANKS_MACRO compiled\n\nSQL&gt; \nSQL&gt; select add_blanks_macro(lower_macro(ename)) from emp;\n\nError starting at line : 23 in command -\nselect add_blanks_macro(lower_macro(ename)) from emp\nError at Command Line : 23 Column : 1\nError report -\nSQL Error: ORA-64630: unsupported use of SQL macro: use of nested scalar SQL macro is not supported\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 8: nesting of scalar SQL macros is not supported<\/em><\/strong><\/p>\n\n\n\n<p>Instead, calling a SQL macro in the return string from another one will work. I haven&#8217;t found, or perhaps overlooked, where this was explicitly documented, but this type of nesting can be found in the examples in the documentation. Furthermore, the presence of this error <strong><a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/23\/errmg\/ORA-60001.html#GUID-9B78A028-D760-4810-9CFC-9013FBD1FCC9\" target=\"_blank\" rel=\"noreferrer noopener\">ORA-64627: cycle detected in SQL macro expansion<\/a><\/strong>, tells me that it is intended to nest macros this way.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; create or replace function  add_blanks_lower_macro (p_param in varchar2) \n  2  return varchar2 sql_macro(scalar) as\n  3  begin\n  4     return q&#039;&#x5B;regexp_replace(lower_macro(p_param),&#039;(.)&#039;,&#039;\\1 &#039;)]&#039;;\n  5  end;\n  6  \/\n\nFunction ADD_BLANKS_LOWER_MACRO compiled\n\nSQL&gt; select add_blanks_lower_macro(ename) ename from emp;\n\nENAME       \n------------\ns m i t h \na l l e n \nw a r d \nj o n e s \nm a r t i n \nb l a k e \nc l a r k \n...\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 9: but nesting in the macro body is supported<\/em><\/strong><\/p>\n\n\n\n<p>Of course, this limits you in terms of interoperability of your SQL macros. You cannot develop a lot of small modular macros and then use them how you need including nesting. Instead, you have to plan for useful combinations upfront and create separate dedicated macros for these combinations (consider the name <strong><em>add_blanks_lower_macro<\/em><\/strong> telling about <strong>two<\/strong> defined operations). Nevertheless, if you identified the useful combination, you don&#8217;t have to duplicate code inside its implementation. Hmm&#8230; OK, well&#8230; sort of \ud83d\ude09 You may ask, why not just call both modular macros in the newly defined <strong><em>add_blanks_lower_macro<\/em><\/strong>, instead of re-implementing the REGEXP_REPLACE in it? Because you cannot! Just single nesting at once is allowed. Following this rule, the SQL engine will never see two nested macros, but instead an a SQL expression after first expansion which can contain a SQL macro call, which in turn will be expanded and can contain a macro call and so on. Consider second example in Listing 10.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; create or replace function  add_blanks_lower_slim_macro (p_param in varchar2) \n  2  return varchar2 sql_macro(scalar) as\n  3  begin\n  4     return &#039;add_blanks_macro(lower_macro(p_param))&#039;;\n  5  end;\n  6  \/\n\nFunction ADD_BLANKS_LOWER_SLIM_MACRO compiled\n\nSQL&gt; select add_blanks_lower_slim_macro(ename) ename from emp;\n\nError starting at line : 48 in command -\nselect add_blanks_lower_slim_macro(ename) ename from emp\nError at Command Line : 48 Column : 1\nError report -\nSQL Error: ORA-64630: unsupported use of SQL macro: use of nested scalar SQL macro is not supported\n64630. 00000 -  &quot;unsupported use of SQL macro: %s&quot;\n*Cause:    \n*Action:\nSQL&gt; \nSQL&gt; \nSQL&gt; create or replace function upper_add_blanks_lower_macro (p_param in varchar2) \n  2  return varchar2 sql_macro(scalar) as\n  3  begin\n  4     return &#039;upper(add_blanks_lower_macro(p_param))&#039;;\n  5  end;\n  6  \/\n\nFunction UPPER_ADD_BLANKS_LOWER_MACRO compiled\n\nSQL&gt; \nSQL&gt; set echo on\nSQL&gt; column ename format a12\nSQL&gt; select upper_add_blanks_lower_macro(ename) ename from emp;\n\nENAME       \n------------\nS M I T H \nA L L E N \nW A R D \nJ O N E S \nM A R T I N \nB L A K E \nC L A R K \nS C O T T \n<\/pre><\/div>\n\n\n<p><strong><em>Listing 10: only single nesting at once<\/em><\/strong><\/p>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Nesting of table macros<\/h4>\n\n\n\n<p>What about table macros? You can nest them this way too.  Consider Listing 11: we want to combine a table macro TOP_N which helps us to limit the rows in the result of a query with another macro that can the columns shown in the result set. Again, you cannot just implement to macros for these two tasks and then define the third macro that combines these two ones. Only single nesting at once is possible this way. So we implement the TOP_N macro as defined by its task and then we have to &#8220;sacrifice&#8221; another one and it will contain its own logic to filter out the columns as well as calling the nested TOP_N macro. We will call it EXCEPT_TOP_N respectively to indicate that it encapsulates two tasks at once.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [30,31,32,39]; title: ; notranslate\" title=\"\">\nSQL&gt; create or replace function top_n (p_tab in dbms_tf.table_t\n  2                                  , p_limit in number\n  3                                  , p_order in dbms_tf.columns_t\n  4                                  ) \n  5  return varchar2 sql_macro is \n  6  v_order_list varchar2(2000);\n  7  begin\n  8    -- turn PL\/SQL table to comma separated list for ORDER BY clause\n  9    select listagg(replace(column_value,&#039;&quot;&#039;),&#039;,&#039;) into v_order_list from table (p_order);\n 10    return &#039;SELECT * FROM p_tab ORDER BY &#039;||v_order_list||\n 11           &#039; FETCH FIRST p_limit ROWS ONLY&#039;;\n 12  end;\n 13  \/\n\nFunction TOP_N compiled\n\nSQL&gt; create or replace function except_top_n (p_tab in dbms_tf.table_t\n  2                                  , p_limit in number\n  3                                  , p_order in dbms_tf.columns_t\n  4                                  , p_except in dbms_tf.columns_t) \n  5  return varchar2 sql_macro is \n  6  v_order_list varchar2(2000);\n  7  v_select_list varchar2(2000);\n  8  all_cols  dbms_tf.columns_t := dbms_tf.columns_t();\n  9  begin\n 10    for i in 1..p_tab.column.count loop\n 11      all_cols.extend;\n 12      all_cols(i):=p_tab.column(i).description.name;  \n 13    end loop;\n 14    select listagg(replace(column_value,&#039;&quot;&#039;),&#039;,&#039;) \n 15    into v_order_list \n 16    from table (p_order);\n 17  \n 18    select listagg(replace(column_value,&#039;&quot;&#039;),&#039;,&#039;) \n 19    into v_select_list \n 20    from (select * from table (all_cols) \n 21          minus \n 22          select * from table (p_except));\n 23    return &#039;SELECT &#039;||v_select_list||&#039; FROM top_n(p_tab, p_limit, columns(&#039;||v_order_list||&#039;))&#039;;\n 24  end;\n 25  \/\n\nFunction EXCEPT_TOP_N compiled\n\nSQL&gt; select * from except_top_n(emp, 10, columns (job, empno), columns (comm, sal));\n\n     EMPNO ENAME        JOB              MGR HIREDATE       DEPTNO\n---------- ------------ --------- ---------- ---------- ----------\n      7788 SCOTT        ANALYST         7566 1987-04-19         20\n      7902 FORD         ANALYST         7566 1981-12-03         20\n      7369 SMITH        CLERK           7902 1980-12-17         20\n      7876 ADAMS        CLERK           7788 1987-05-23         20\n      7900 JAMES        CLERK           7698 1981-12-03         30\n      7934 MILLER       CLERK           7782 1982-01-23         10\n      7566 JONES        MANAGER         7839 1981-04-02         20\n      7698 BLAKE        MANAGER         7839 1981-05-01         30\n      7782 CLARK        MANAGER         7839 1981-06-09         10\n      7839 KING         PRESIDENT            1981-11-17         10\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 11: Nesting of table SQL macros<\/em><\/strong><\/p>\n\n\n\n<p>As you can see, passing the parameters become a little bit trickier this time. The scalar and table parameters are passed seamlessly but this doesn&#8217;t work with column parameters. No substitution takes place for them when leaving the SQL macro. That&#8217;s why we cannot just put the parameter in the return string like we could do for scalar and table parameters. Instead, we have to take the columns parameter in the macro body &#8211; which is available for us as a table <strong>DBMS_TF.COLUMNS_T<\/strong> &#8211; and process it as we need to generate a column list <strong>as a string<\/strong> and then <strong>concatenate<\/strong> it with the result string. This means we have to construct a COLUMNS parameter in this way again if we have to pass it to the nested SQL macro. Look at the Listing 11 again: the lines 30-32 are actually not needed to accomplish the task of the second macro, they are just there to be able to construct the COLUMNS parameter to pass it into the TOP_N macro in the line 39.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Conclusion<\/h4>\n\n\n\n<p>There are actually more possibilities to call SQL macros as explicitly mentioned in the documentation. It is a matter of having a good use case to use them on this unusual places. It is a pity that it is not easily possible to define a set of fine-granular SQL macros encapsulating just one task and then combine them as desired. The nesting in the body of a macro is possible but often requires more effort and up-front planning.<\/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\/sql-macros-part-2-parameters-and-parsing\/\" target=\"_blank\">SQL Macros Part 2 &#8211; Passing of Parameters and Parsing<\/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\/sql-macros-some-less-obvious-facts-part-2\/\" target=\"_blank\">SQL Macros \u2013 Some Less Obvious Facts (Part 2)<\/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\/sql-macros-in-oracle-19c\/\" target=\"_blank\">SQL macros in Oracle 19c<\/a><\/div><\/li><\/ul><\/div>","protected":false},"excerpt":{"rendered":"<p>Since I have written a lot about SQL macros, I have tested some less obvious, sometimes surprising cases. But expanding about them in the original posts would just overload them, so I decided to make a dedicated post for that. The other good reason for this is that the SQL macros are, in my opinion, [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":2006,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,3,100],"tags":[148,103],"class_list":["post-1910","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","category-sql","category-sql-macros","tag-nesting","tag-sql-macros"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1910","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=1910"}],"version-history":[{"count":53,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1910\/revisions"}],"predecessor-version":[{"id":2098,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1910\/revisions\/2098"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media\/2006"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media?parent=1910"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/categories?post=1910"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/tags?post=1910"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}