{"id":2046,"date":"2024-01-05T17:19:25","date_gmt":"2024-01-05T15:19:25","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=2046"},"modified":"2024-01-05T17:19:27","modified_gmt":"2024-01-05T15:19:27","slug":"sql-macros-some-less-obvious-facts-part-2","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/sql-macros-some-less-obvious-facts-part-2\/","title":{"rendered":"SQL Macros \u2013 Some Less Obvious Facts (Part 2)"},"content":{"rendered":"\n<p>This second part of the &#8220;less obvious facts&#8221; series is about the COLUMNS pseudo-operator. It has been introduced in 18c as one of the <a rel=\"noreferrer noopener\" href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/23\/lnpls\/plsql-optimization-and-tuning.html#GUID-A3123AB1-E06D-4E00-A856-6052527482CE\" target=\"_blank\"> variadic pseudo-operators<\/a>, intended to operate with a variable number of operands.  Unfortunately, no other pseudo-operators have been introduced since then, so that it is worth looking at what exactly the COLUMNS operator is, how to use it and how it can potentially be &#8220;misused&#8221;. <\/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 much time to read a long story here are the key facts you can read and decide whether you should take the time ;-):<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>COLUMNS is intended to pass columns lists, it will be converted to a collection at parse time &#8211; you have to process this collection accordingly<\/li>\n\n\n\n<li>As long as you are not using quoted identifiers  for the columns involved in your query you can pass some additional information like ASC\/DESC if you enclose in double quotes by yourself<\/li>\n\n\n\n<li>COLUMNS can also be used for passing column names<strong> along with data types<\/strong><\/li>\n\n\n\n<li>COLUMNS operator can be used with scalar SQL macros as well<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">COLUMNS pseudo operator<\/h4>\n\n\n\n<p>It was originally intended for use with PTF&#8217;s. As we can see, these two topics are closely linked. The SQL macros seem to use the PTF infrastructure, but this is not clearly mentioned in the documentation. One of the places where you can clearly see this is the COLUMNS operator.<\/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\/blog_figures.png\"><img loading=\"lazy\" decoding=\"async\" width=\"870\" height=\"642\" src=\"https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2023\/12\/blog_figures.png\" alt=\"\" class=\"wp-image-2040\" srcset=\"https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2023\/12\/blog_figures.png 870w, https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2023\/12\/blog_figures-300x221.png 300w, https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2023\/12\/blog_figures-768x567.png 768w, https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2023\/12\/blog_figures-624x460.png 624w\" sizes=\"auto, (max-width: 870px) 100vw, 870px\" \/><\/a><figcaption class=\"wp-element-caption\">Figure 2: COLUMNS pseudo operator<\/figcaption><\/figure>\n\n\n\n<p>Consider the Figure 2: <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>COLUMNS is intended to pass column lists<\/li>\n\n\n\n<li>Despite of this, no check is conducted that columns passed exist in a table. In fact, the database  doesn&#8217;t even know what table is meant<\/li>\n\n\n\n<li>Only literals are allowed, no bind variables. <\/li>\n\n\n\n<li>No empty list &#8211; at least one element is required<\/li>\n\n\n\n<li>The elements should be valid SQL identifiers. You can quote them if needed. That means, all characters except CHR(0) and double quotes are allowed<\/li>\n\n\n\n<li>At parsing phase the list passed with COLUMNS pseudo-operator will be converted by the database to the data type <strong><em>DBMS_TF.COLUMNS_T<\/em><\/strong> which is a table of <strong><em>DBMS_QUOTED_ID<\/em><\/strong>. All elements will be enclosed in double quotes if not already. There is also another possible use with different data type: see further down in this post.<\/li>\n\n\n\n<li>You can access this PL\/SQL table as usual in your macro body. How and whether you use it in your result expression is entirely up to you<\/li>\n\n\n\n<li>The resulting SQL which will be executed doesn&#8217;t contain the COLUMNS-operator anymore, but completely dependent from what you&#8217;ve done with this list in your SQL macro body.<\/li>\n\n\n\n<li>You can&#8217;t just leave the parameter name in the result string of the macro because there is no substitution that takes place like with scalar or table parameters. You have to concatenate for this<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">Passing more than just column names?<\/h4>\n\n\n\n<p>What if I want to pass more than just a plain column list? For example I want to pass a column list for an ORDER BY clause. This can additionally contain  <strong><em>ASC\/DESC<\/em><\/strong> or <strong><em>NULLS LAST\/FIRST<\/em><\/strong>.  Hmm&#8230; Actually, you can do this, as long as you are <strong>not<\/strong> using quoted identifiers for the column names. Just add what you need and enclose the whole expression in double quotes. Consider Listing 1: <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [16]; title: ; notranslate\" title=\"\">\ncreate or replace function top_n (p_tab in dbms_tf.table_t\n                                , p_limit in number\n                                , p_order in dbms_tf.columns_t) \nreturn varchar2 sql_macro is \nv_order_list varchar2(2000);\nbegin\n  -- turn PL\/SQL table to comma separated list for ORDER BY clause\n  select listagg(replace(column_value,&#039;&quot;&#039;),&#039;,&#039;) into v_order_list from table (p_order);\n  return &#039;SELECT * FROM top_n.p_tab ORDER BY &#039;||v_order_list||\n         &#039; FETCH FIRST top_n.p_limit ROWS ONLY&#039;;\nend;\n\/\n\nSQL&gt; \nSQL&gt; select * \n  2  from top_n(emp, 7, columns(&quot;comm desc nulls last&quot;)) e;\n\n     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO\n---------- ---------- --------- ---------- ---------- ---------- ---------- ----------\n      7654 MARTIN     SALESMAN        7698 1981-09-28       1250       1400         30\n      7521 WARD       SALESMAN        7698 1981-02-22       1250        500         30\n      7499 ALLEN      SALESMAN        7698 1981-02-20       1600        300         30\n      7844 TURNER     SALESMAN        7698 1981-09-08       1500          0         30\n      7369 SMITH      CLERK           7902 1980-12-17        800                    20\n      7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30\n      7782 CLARK      MANAGER         7839 1981-06-09       2450                    10\n\n7 rows selected. \n\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 1: using double quotes to pass more than just a column name<\/em><\/strong><\/p>\n\n\n\n<p>It is up to you to call it a &#8220;hack&#8221; or not. Of course, we could as well pass the ORDER BY clause as a string. Keep in mind that in this case you can&#8217;t process this parameter in any way, because it would be NULL inside your macro body.  Everything you can do is to hand it over to the return string of the macro. Or we could just use another &#8220;half-hack&#8221; and pass the string as an element of the collection (table of varchar2) which will be fully visible inside the macro.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">COLUMNS with data types<\/h4>\n\n\n\n<p>You can pass column names along with their (scalar) data types using COLUMNS pseudo-operator. The section of the documentation about this is very easy to overlook and I haven&#8217;t found any example of this use. In this case the input via COLUMNS is turned into <strong><em>DBMS_TF.COLUMNS_WITH_TYPE_T<\/em><\/strong> data type, which is not a simple list of quoted SQL identifiers more, but a complex record structure. Personally, I didn&#8217;t come up with a good use case for that yet. The one that came in mind is casting the input to the defined data types. See the Listing 2, which handles just VARCHAR2 and NUMBER datatypes to demonstrate the idea. We pass all columns as is, unless we have found a match in the passed COLUMNS operator along with the desired data types.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [35]; title: ; notranslate\" title=\"\">\nSQL&gt; create or replace function cols_type (p_tab in dbms_tf.table_t\n  2                                  , p_cols in dbms_tf.COLUMNS_WITH_TYPE_T) \n  3  return varchar2 sql_macro is \n  4  v_cols varchar2(10000);\n  5  v_found boolean;\n  6  begin\n  7    for k in 1..p_tab.column.count loop\n  8      v_found := false;\n  9      for i in 1..p_cols.count loop\n 10        if p_cols(i).name = p_tab.column(k).description.name then \n 11          case  p_cols(i).type \n 12            when  dbms_tf.TYPE_VARCHAR2 then  \n 13              v_cols := v_cols || &#039;, cast(&#039;||p_cols(i).name||&#039; as varchar2(&#039;||p_cols(i).max_len||&#039;) ) as &#039;||p_cols(i).name;\n 14            when  dbms_tf.TYPE_NUMBER then  \n 15              v_cols := v_cols || &#039;, cast(&#039;||p_cols(i).name||&#039; as number(&#039;||p_cols(i).precision||&#039;,&#039;||p_cols(i).scale||&#039;) ) as &#039;||p_cols(i).name;\n 16          end case;\n 17          v_found := true;\n 18          exit;\n 19        end if;\n 20      end loop;\n 21      if not v_found then     \n 22        v_cols := v_cols || &#039;, &#039;||p_tab.column(k).description.name;  \n 23      end if;\n 24    end loop;\n 25    v_cols := substr(v_cols,2);\n 26    return &#039;SELECT &#039;||v_cols||&#039; FROM p_tab &#039;;\n 27  end;\n 28  \/\n\nFunction COLS_TYPE compiled\n\nSQL&gt; \nSQL&gt; set echo on\nSQL&gt; select * \n2  from cols_type(emp, columns(empno varchar2(2), job varchar2(3), sal number(6,-2)));\n\nEM ENAME      JOB        MGR HIREDATE          SAL       COMM     DEPTNO\n-- ---------- --- ---------- ---------- ---------- ---------- ----------\n73 SMITH      CLE       7902 1980-12-17        800                    20\n74 ALLEN      SAL       7698 1981-02-20       1600        300         30\n75 WARD       SAL       7698 1981-02-22       1300        500         30\n75 JONES      MAN       7839 1981-04-02       3000                    20\n76 MARTIN     SAL       7698 1981-09-28       1300       1400         30\n76 BLAKE      MAN       7839 1981-05-01       2900                    30\n77 CLARK      MAN       7839 1981-06-09       2500                    10\n77 SCOTT      ANA       7566 1987-04-19       3000                    20\n78 KING       PRE            1981-11-17       5000                    10\n78 TURNER     SAL       7698 1981-09-08       1500          0         30\n78 ADAMS      CLE       7788 1987-05-23       1100                    20\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 2: COLUMNS with data types<\/em><\/strong><\/p>\n\n\n\n<p><\/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<h4 class=\"wp-block-heading\">Using COLUMNS with scalar SQL macros<\/h4>\n\n\n\n<p>It is not possible to use table operator with scalar macros &#8211; it is documented or at least caught by compilation exception PLS-00777: scalar SQL macro cannot have argument of type DBMS_TF.TABLE_T. But  nothing prevents the use of COLUMN parameters with scalar macros if you need to. What can be the use case? Something where you benefit from operating with a variable number of operands of the same kind. In my career as data engineer I often had to deal with wide tables consisting of hundreds of  numeric fields which have to be summarized to build up some KPI&#8217;s. I mean, not across rows, but across columns of the same row. I think I would have benefited from the function like in Listing 3. It adds some useful features like making the column list unique (if you end up with the list of 30-40 columns it is pretty easy to overlook duplicates) and surrounding the columns with NVL().<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [6,8]; title: ; notranslate\" title=\"\">\nSQL&gt; create or replace function  sum_up (p_sum_cols in  dbms_tf.columns_t) \n  2  return varchar2 sql_macro(scalar) as\n  3  v_result varchar2(32767);\n  4  v_sum_cols dbms_tf.columns_t;\n  5  begin\n  6      v_sum_cols := set (p_sum_cols); \n  7      for i in 1..v_sum_cols.count loop\n  8          v_result := v_result||&#039;+NVL(&#039;||v_sum_cols(i)||&#039;,0)&#039;;\n  9      end loop;\n 10      return substr(v_result,2);\n 11  end;\n 12  \/\n\nFunction SUM_UP compiled\n\nSQL&gt; select empno, ename, sal, comm, sum_up(columns(sal, sal, comm)) result \n  2  from emp e;\n\n     EMPNO ENAME             SAL       COMM     RESULT\n---------- ---------- ---------- ---------- ----------\n      7369 SMITH             800                   800\n      7499 ALLEN            1600        300       1900\n      7521 WARD             1250        500       1750\n      7566 JONES            2975                  2975\n      7654 MARTIN           1250       1400       2650\n      7698 BLAKE            2850                  2850\n...\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 3: Scalar SQL macros with COLUMNS pseudo operator<\/em><\/strong><\/p>\n\n\n\n<p>What about a REPLACE function which is able to handle not just one pair of search and replace expressions but an &#8220;infinite&#8221; number of them?<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; create or replace function  multireplace (p_input_str in varchar2\n  2                                          , p_replace_chain in  dbms_tf.columns_t) \n  3  return varchar2 sql_macro(scalar) as\n  5  v_result varchar2(32767);\n  6  v_result_mid varchar2(32767);\n  7  begin\n  8      for i in 1..p_replace_chain.count loop\n  9        if mod(i,2) = 0 then \n 10          v_result := v_result||&#039;replace(&#039;;\n 11          v_result_mid := v_result_mid||&#039;,&#039;&#039;&#039;||replace(p_replace_chain(i-1),&#039;&quot;&#039;)\n 12                          ||&#039;&#039;&#039;,&#039;&#039;&#039;||replace(p_replace_chain(i),&#039;&quot;&#039;)||&#039;&#039;&#039;)&#039;;\n 13        end if;\n 14      end loop;\n 15      return v_result||&#039;p_input_str&#039;||v_result_mid;\n 16  end;\n 17  \/\n\nFunction MULTIREPLACE compiled\n\nSQL&gt; with t_input as (select &#039;Use nested REPLACE functions&#039; str)\n  2  select multireplace(str, COLUMNS(&quot;REPLACE&quot;, &quot;function&quot;, \n  3                                   &quot;functions&quot;, &quot;instead&quot;,\n  4                                   &quot;nested&quot;, &quot;MULTIREPLACE&quot;)) result\n  5  from t_input;\n\nRESULT                           \n---------------------------------\nUse MULTIREPLACE function instead\n\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 4: &#8220;misusing&#8221; the COLUMNS operator &#8211; passing something completely different than columns<\/em><\/strong><\/p>\n\n\n\n<p>Well, after all, I <strong>don&#8217;t think<\/strong> it would be a suitable example and I <strong>don&#8217;t advocate<\/strong> to use the operator this way. The first problem is that you can&#8217;t pass NULL\/ empty strings or strings containing double quotes. Maybe I&#8217;m wrong, but misusing of COLUMNS operator this way looks more like a hack to me. Especially if you consider that it is just as well possible to implement this with user defined collection types instead of COLUMNS:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; set echo on\nSQL&gt; create or replace function  multireplace_nt (p_input_str in varchar2\n  2                                          , p_replace_chain in  sys.odcivarchar2list) \n  3  return varchar2 sql_macro(scalar) as\n  4  v_result varchar2(32767);\n  5  v_result_mid varchar2(32767);\n  6  begin\n  7      for i in 1..p_replace_chain.count loop\n  8        if mod(i,2) = 0 then \n  9          v_result := v_result||&#039;replace(&#039;;\n 10          v_result_mid := v_result_mid||&#039;,&#039;&#039;&#039;||replace(p_replace_chain(i-1),&#039;&quot;&#039;)\n 11                          ||&#039;&#039;&#039;,&#039;&#039;&#039;||replace(p_replace_chain(i),&#039;&quot;&#039;)||&#039;&#039;&#039;)&#039;;\n 12        end if;\n 13      end loop;\n 14      return v_result||&#039;p_input_str&#039;||v_result_mid;\n 15  end;\n 16  \/\n\nFunction MULTIREPLACE_NT compiled\n\nSQL&gt; \nSQL&gt; with t_input as (select &#039;Use nested REPLACE functions&#039; str)\n  2  select multireplace_nt(str, sys.odcivarchar2list(&#039;REPLACE&#039;, &#039;&#039;, \n  3                                                   &#039;functions&#039;, &#039;instead&#039;, \n  4                                                   &#039;nested&#039;, &#039;MULTIREPLACE&#039;)) result\n  5  from t_input;\n\nRESULT                   \n-------------------------\nUse MULTIREPLACE  instead \n<\/pre><\/div>\n\n\n<p><strong><em>Listing 5: Using a pre-defined collection sys.odcivarchar2list instead of misusing COLUMNS<\/em><\/strong><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Conclusion<\/h4>\n\n\n\n<p>The way COLUMNS pseudo operator was described as one of the <a rel=\"noreferrer noopener\" href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/lnpls\/plsql-optimization-and-tuning.html#GUID-A3123AB1-E06D-4E00-A856-6052527482CE\" target=\"_blank\">variadic-pseudo-operators<\/a>  in the 18c documentation back then made me think that there will be more to come. I don&#8217;t know if it was meant that way, but no more have been introduced so far. So we looked at this one, how it works, how to use it and how to do a little bit more with it.<\/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\/sql-macros-some-less-obvious-facts-part-1\/\" target=\"_blank\">SQL Macros &#8211; Some Less Obvious Facts (Part 1)<\/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><\/ul><\/div>","protected":false},"excerpt":{"rendered":"<p>This second part of the &#8220;less obvious facts&#8221; series is about the COLUMNS pseudo-operator. It has been introduced in 18c as one of the variadic pseudo-operators, intended to operate with a variable number of operands. Unfortunately, no other pseudo-operators have been introduced since then, so that it is worth looking at what exactly the COLUMNS [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":2073,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,2,3,100],"tags":[147,103],"class_list":["post-2046","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-general","category-oracle","category-sql","category-sql-macros","tag-columns","tag-sql-macros"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/2046","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=2046"}],"version-history":[{"count":25,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/2046\/revisions"}],"predecessor-version":[{"id":2099,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/2046\/revisions\/2099"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media\/2073"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media?parent=2046"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/categories?post=2046"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/tags?post=2046"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}