{"id":1330,"date":"2020-03-30T18:17:50","date_gmt":"2020-03-30T16:17:50","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=1330"},"modified":"2020-04-20T18:19:48","modified_gmt":"2020-04-20T16:19:48","slug":"dynamic-pivot-with-sql-macros-in-oracle-20c","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/dynamic-pivot-with-sql-macros-in-oracle-20c\/","title":{"rendered":"Dynamic Pivot with SQL Macros in Oracle 20c"},"content":{"rendered":"\n<p>More than a year ago I blogged about my view on <a rel=\"noreferrer noopener\" aria-label=\"using Polymorphic Table Function (PTF) for dynamic pivot. (opens in a new tab)\" href=\"https:\/\/blog.sqlora.com\/en\/dynamic-pivot-with-polymorphic-table-function\/\" target=\"_blank\">using Polymorphic Table Function (PTF) for dynamic pivot<\/a> in Oracle 18c. Actually I was not as optimistic. The solution had at least two significant problems:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> the function will not see any changes on the underlying data until new hard parse of the query<\/li><li>operating on bigger data sets returns multiple result rows: one per 1024 rows (the size of the row sets in PTF), thus requiring some post processing  <\/li><\/ul>\n\n\n\n<p>With Oracle 20c we now have SQL macros and I was curious, whether they can help here.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p>Going ahead, I should say, the solution using SQL macros has some limitations as well. And in my opinion, it is not really viable for daily using. If you just look for waterproof solution working in every situation, stop reading here. If you want to know what  drawbacks it had and why, continue \ud83d\ude09<\/p>\n\n\n\n<p>We will be using the data from previous post for pivoting. First, we&#8217;ll create a table by doing a temporal join: Listing 1 <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; CREATE TABLE emp_joined \nAS\nSELECT * \nFROM   time_machine.temporal_join( t1 =&gt; empv\n                                 , t2 =&gt; deptv\n                                 , t3 =&gt;jobv\n                                 , bk_cols =&gt; columns(empno)\n                                 , join_cols1 =&gt; columns(deptno, deptno)\n                                 , join_cols2 =&gt; columns(jobno, jobno))\nTable created.\nSQL&gt; SELECT empv_empno, empv_ename, jobv_job, valid_from, valid_to FROM emp_joined\nFETCH FIRST 5 ROWS ONLY\n\nEMPV_EMPNO EMPV_ENAME     JOBV_JOB  VALID_FROM VALID_TO \n---------- -------------- --------- ---------- ---------\n      7369 SMITH          CLERK     17-DEC-80  31-DEC-89\n      7499 ALLEN          SALESMAN  20-FEB-81  31-DEC-89\n      7521 WARD           SALESMAN  22-FEB-81  31-DEC-89\n      7566 JONES          MANAGER   02-APR-81  31-DEC-89\n      7654 MARTIN         SALESMAN  28-SEP-81  31-DEC-89\n\n5 rows selected.\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 1: Creating test data<\/em><\/strong><\/p>\n\n\n\n<p>Based on this table, let&#8217;s count how oft we see the jobs CLERK and ANALYST. The SQL query to do this with PIVOT operator introduced in 11g:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; SELECT * \nFROM   (SELECT empv_empno, jobv_job \n        FROM   emp_joined)\nPIVOT  ( \n  COUNT( DISTINCT empv_empno)  \n  FOR (jobv_job) IN ( &#039;CLERK&#039; clerk, &#039;ANALYST&#039; analyst )\n)\n\n     CLERK    ANALYST\n---------- ----------\n         4          2\n1 row selected.\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 2: Static query using PIVOT operator<\/em><\/strong><\/p>\n\n\n\n<p>The column list inside of PIVOT is static. We have to know what jobs are there. If we wanted to count them all, we had to list them all at the time of writing this query. That&#8217;s why everyone is so keen on dynamic pivot. Wouldn&#8217;t it be nice to just call a SQL macro function which returns  these columns instead of explicitly list them? Something like this:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; SELECT * \nFROM   (SELECT empv_empno, jobv_job \n        FROM   emp_joined)\nPIVOT  ( \n  COUNT( DISTINCT empv_empno)  \n  FOR (jobv_job) IN ( get_columns_macro() )\n)\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 3: This would not work<\/em><\/strong><\/p>\n\n\n\n<p>We can try to develop a macro function. It is quite trivial, but calling the function this way will not work:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; CREATE FUNCTION get_pivot_in_clause (p_tab DBMS_TF.TABLE_T\n                                        , p_pivot_column DBMS_TF.COLUMNS_T\n                                        )\nRETURN VARCHAR2 SQL_MACRO\nIS\nv_sql varchar2(1000);\nBEGIN\n  v_sql := q&#039;!select listagg(distinct upper(&#039;&#039;&#039;&#039;||%COL_PLACEHOLDER%||&#039;&#039;&#039; &#039;||%COL_PLACEHOLDER% ),&#039;,&#039;) pivot_in_clause from p_tab!&#039;; \n  return replace (v_sql, &#039;%COL_PLACEHOLDER%&#039;, replace(p_pivot_column(1),&#039;&quot;&#039;))||p_tab.table_name;\nEND;\nFunction created.\n\nSQL&gt; select * from get_pivot_in_clause (emp_joined, columns(jobv_job))\n\nPIVOT_IN_CLAUSE                                                                 \n--------------------------------------------------------------------------------\n&#039;CLERK&#039; CLERK,&#039;SALESMAN&#039; SALESMAN,&#039;MANAGER&#039; MANAGER,&#039;ANALYST&#039; ANALYST,&#039;PRESIDENT\n&#039; PRESIDENT                                                                     \n                                                                                \n1 row selected.\n\n\nSELECT * \nFROM   (SELECT empv_empno, jobv_job \n        FROM   emp_joined)\nPIVOT  ( \n  COUNT( DISTINCT empv_empno)  \n  FOR (jobv_job) IN ( get_pivot_in_clause(emp_joined, columns(jobv_job) ) )\n)\n                         *\nError at line 6\nORA-56901: non-constant expression is not allowed for pivot|unpivot values\n\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 5: SQL macro works stand-alone, but calling in PIVOT fails<\/em><\/strong><\/p>\n\n\n\n<p>This doesn&#8217;t work for two reasons actually. You can only call SQL macros where you are allowed to call functions in a SQL. And you are not allowed to do this inside column declaration in PIVOT. This is what ORA-56901 is saying. In fact, I think Oracle don&#8217;t even check that the function is a SQL macro. We are experiencing the same restriction known since 11g.  The other reason why it wouldn&#8217;t work even if  Oracle would not throw ORA-56901 is that you only can call table macros in FROM clause.<\/p>\n\n\n\n<p>The next try is to provide a table macro which returns the whole pivot query. Let&#8217;s start with minimalist requirements and simply reproduce the query from Listing 1 looking at the syntax diagram for PIVOT operator. <\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"734\" height=\"138\" src=\"https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2020\/03\/pivot_diag.png\" alt=\"\" class=\"wp-image-1429\" srcset=\"https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2020\/03\/pivot_diag.png 734w, https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2020\/03\/pivot_diag-300x56.png 300w, https:\/\/blog.sqlora.com\/en\/wp-content\/uploads\/sites\/2\/2020\/03\/pivot_diag-624x117.png 624w\" sizes=\"auto, (max-width: 734px) 100vw, 734px\" \/><\/figure>\n\n\n\n<p>For simplicity we suppose we only want one known aggregate function (COUNT) on one column and we also have just one pivot column. Let&#8217;s look at the implementation in Listing 6. <\/p>\n\n\n\n<p>We need following parameter:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><code><strong>p_tab DBMS_TF.TABLE_T<\/strong><\/code> &#8211;  the table to select from<\/li><li><code><strong>p_pivot_colum DBMS_TF.COLUMNS_T<\/strong><\/code> &#8211; column(s) in <em>pivot_for_clause<\/em><\/li><li><code><strong>p_agg_cols DBMS_TF.COLUMNS_T<\/strong><\/code> &#8211; columns(s), aggregate function operates on <\/li><\/ul>\n\n\n\n<p>Lines 10-12 : execute the SQM function we created earlier to get the pivot_in_clause<br>Line 13-17: build the PIVOT statement and return the string<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; CREATE OR REPLACE FUNCTION dynamic_pivot (p_tab DBMS_TF.TABLE_T\n                                        , p_pivot_column DBMS_TF.COLUMNS_T\n                                        , p_agg_cols DBMS_TF.COLUMNS_T\n                                        )\nRETURN VARCHAR2 SQL_MACRO\nIS\nv_cols varchar2(2000);\nv_sql varchar2(500);\nBEGIN\n  v_sql := &#039;select pivot_in_clause from get_pivot_in_clause(&#039;\n          ||p_tab.table_name||&#039;, columns(&#039;||p_pivot_column(1)||&#039;) ) &#039;; \n  EXECUTE IMMEDIATE v_sql INTO v_cols;\n  v_sql :=  &#039;select * from (select &#039;||p_pivot_column(1)||\n               &#039;, &#039;||p_agg_cols(1)||&#039; from p_tab)\n         pivot  ( count(distinct &#039;||p_agg_cols(1)|| &#039;)&#039;\n         ||&#039; for (&#039;||p_pivot_column(1)||&#039;) in (  &#039;||v_cols||&#039; ) )&#039;;\n  RETURN v_sql;         \nEND;\nFunction created.\n\nSQL&gt; SELECT * \nFROM   dynamic_pivot(emp_joined\n                   , COLUMNS(JOBV_JOB)\n                   , COLUMNS(empv_empno)\n                   )\n\n     CLERK   SALESMAN    MANAGER    ANALYST  PRESIDENT\n---------- ---------- ---------- ---------- ----------\n         4          4          3          2          1\n1 row selected.\n\nSQL&gt; UPDATE emp_joined SET jobv_job = &#039;DBA&#039; \nWHERE  empv_empvid = 1\n1 row updated.\n\nSQL&gt; -- Where are my DBA&#039;s?\nSQL&gt; SELECT * \nFROM   dynamic_pivot(emp_joined\n                   , COLUMNS(JOBV_JOB)\n                   , COLUMNS(empv_empno)\n                   )\n\n     CLERK   SALESMAN    MANAGER    ANALYST  PRESIDENT\n---------- ---------- ---------- ---------- ----------\n         4          4          3          2          1\n1 row selected.\n\nSQL&gt; -- Purge the SQL out if shared pool\nSQL&gt; SELECT address, hash_value FROM   V$SQLAREA ...\n\nADDRESS          HASH_VALUE\n---------------- ----------\n00000001545B6C40 3320021849\n1 row selected.\n\nSQL&gt; exec sys.DBMS_SHARED_POOL.PURGE (&#039;00000001545B6C40,\t3320021849&#039;, &#039;C&#039;)\n PL\/SQL procedure successfully completed.\n\nSQL&gt; -- After hard parse we will see DBA:\nSQL&gt; SELECT * \nFROM   dynamic_pivot(emp_joined\n                   , COLUMNS(JOBV_JOB)\n                   , COLUMNS(empv_empno)\n                   )\n\n       DBA   SALESMAN    MANAGER    ANALYST  PRESIDENT      CLERK\n---------- ---------- ---------- ---------- ---------- ----------\n         1          4          3          2          1          4\n1 row selected.\n\nSQL&gt; ROLLBACK\nRollback complete.\n\nSQL&gt; -- There are no DBA&#039;s now, but we still see the column!\nSQL&gt; SELECT * \nFROM   dynamic_pivot(emp_joined\n                   , COLUMNS(JOBV_JOB)\n                   , COLUMNS(empv_empno)\n                   )\n\n       DBA   SALESMAN    MANAGER    ANALYST  PRESIDENT      CLERK\n---------- ---------- ---------- ---------- ---------- ----------\n         0          4          3          2          1          4\n1 row selected.\n\nSQL&gt; -- It disappears after hard parse\nSQL&gt; exec sys.DBMS_SHARED_POOL.PURGE (&#039;00000001545B6C40,\t3320021849&#039;, &#039;C&#039;)\n PL\/SQL procedure successfully completed.\n\nSQL&gt; SELECT * \nFROM   dynamic_pivot(emp_joined\n                   , COLUMNS(JOBV_JOB)\n                   , COLUMNS(empv_empno)\n                   )\n\n     CLERK   SALESMAN    MANAGER    ANALYST  PRESIDENT\n---------- ---------- ---------- ---------- ----------\n         4          4          3          2          1\n1 row selected.\n\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 6: SQL macro works, but it is &#8220;DML-agnostic&#8221;<\/em><\/strong><\/p>\n\n\n\n<p>It works, but still has two major drawbacks. As you can see in Listing 6, any changes in your data are only visible after the hard parse. This remains true with SQL macros as it was with PTF solution. <\/p>\n\n\n\n<p>Another problem is that we cannot use subqueries, neither inline, nor named subqueries. Look at the line 11 of Listing 6. There, we have to concatenate the table name (<code><strong>p_tab.table_name<\/strong><\/code>) into the statement to get column names. That&#8217;s because inside  the SQL macro no parameter substitution takes place. It&#8217;s only the case on exit of the SQM function. Just like in line 14, we are referencing <code><strong>p_tab<\/strong><\/code> directly in a string and on returning of this string Oracle will automatically substitute the actual table name or subquery(!). But again, it happens only with a string in RETURN-Operator.  In line 11 we have to concatenate but, unfortunately,  if using subquery   <code><strong>p_tab.table_name<\/strong><\/code> is NULL. <\/p>\n\n\n\n<p>This leads to a very annoying limitation of this approach: it can only be used with real tables or (materialized) views. Moreover, in a previous post we saw that you can&#8217;t nest table macros in one call. What we see here, means actually that we cannot call one table macro inside of another, too. I think,  this would be very reasonable feature request for next releases.<\/p>\n\n\n\n<p>But at least  we could lift one limitation of the PTF-approach: we have no problems with bigger tables here. As you can see in Listing 7 we get just one row as expected. I changed the implementation a bit to cater for multiple measures. I leave it to you to make it complete and add the functionality to handle multiple dimensions as well.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; CREATE OR REPLACE FUNCTION dynamic_pivot (p_tab DBMS_TF.TABLE_T\n                                        , p_pivot_column DBMS_TF.COLUMNS_T\n                                        , p_agg_cols DBMS_TF.COLUMNS_T\n                                        , p_agg_func sys.ODCIVarchar2List)\nreturn varchar2 sql_macro\nis\nv_cols varchar2(2000);\nv_sql varchar2(500);\nv_agg varchar2(2000);\nv_agg_cols varchar2(2000);\nbegin\n  v_sql := &#039;select pivot_in_clause from get_pivot_in_clause(&#039;||p_tab.table_name||&#039;, columns(&#039;||p_pivot_column(1)||&#039;) ) &#039;; \n  EXECUTE IMMEDIATE v_sql INTO v_cols;\n  for i in 1..p_agg_func.count loop\n    v_agg := v_agg||&#039;,&#039;||p_agg_func(i); \n  end loop;\n  v_agg := ltrim(v_agg, &#039;,&#039;);\n  for i in 1..p_agg_cols.count loop\n    v_agg_cols := v_agg_cols||&#039;,&#039;||p_agg_cols(i); \n  end loop;\n  v_agg := ltrim(v_agg, &#039;,&#039;);\n  v_agg_cols := ltrim(v_agg_cols, &#039;,&#039;);    \n  v_sql :=  &#039;select * from (select &#039;||p_pivot_column(1)||&#039;, &#039;\n                                    ||v_agg_cols||&#039; from p_tab) &#039;\n         ||&#039;  pivot  ( &#039;||v_agg \n         ||&#039; for (&#039;||p_pivot_column(1)||&#039;) in (  &#039;||v_cols||&#039; ) )&#039;;\n  RETURN v_sql;         \nend;\nFunction created.\n\nSQL&gt; create or replace view emp_joined_big as\nselect e.* \nfrom emp_joined e \n     cross join \n     (select level from dual connect by level&amp;lt;=100)\nView created.\n\nSQL&gt; select * from  dynamic_pivot(emp_joined_big\n                           , columns(JOBV_JOB)\n                           , columns(empv_empno, empv_sal)\n                           , sys.ODCIVarchar2List(&#039;count( empv_empno) as cnt&#039;\n                                                , &#039;sum(empv_sal) as sal&#039;))\n\n CLERK_CNT  CLERK_SAL SALESMAN_CNT SALESMAN_SAL MANAGER_CNT MANAGER_SAL ANALYST_CNT ANALYST_SAL PRESIDENT_CNT PRESIDENT_SAL\n---------- ---------- ------------ ------------ ----------- ----------- ----------- ----------- ------------- -------------\n       800     830000          800      1120000         600     1655000         400     1200000           200       1000000\n1 row selected.\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 7: extended SQL macro <\/em><\/strong><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Summary<\/h4>\n\n\n\n<p>The implementation for dynamic PIVOT with SQL macros is much simpler as with <a rel=\"noreferrer noopener\" aria-label=\"PTF (opens in a new tab)\" href=\"https:\/\/livesql.oracle.com\/apex\/livesql\/file\/content_HPN95108FSSZD87PXX7MG3LW3.html\" target=\"_blank\">PTF<\/a>. But it also has limitations: it doesn&#8217;t work with subqueries and doesn&#8217;t reflect any data changes without a hard parse. So I consider this more as an example of what is possible with SQL macros than as a call to use the function seriously. <\/p>\n\n\n\n<p>Nevertheless, there are two reasonable enhancement requests @Oracle:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Please make it possible to pass table parameter on to other SQL macro functions called inside a macro<\/li><li>Please allow for table macro functions inside PIVOT_IN_CLAUSE, after all the expression will remain constant (but dynamically built with the help of a SQL macro) and there will be no reason for  ORA-56901 <\/li><\/ul>\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\/dynamic-pivot-with-polymorphic-table-function\/\" target=\"_blank\">Dynamic Pivot with Polymorphic Table Function?<\/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\/oracle-20c-sql-macros\/\" target=\"_blank\">Oracle 20c: SQL Macros<\/a><\/div><\/li><\/ul><\/div>","protected":false},"excerpt":{"rendered":"<p>More than a year ago I blogged about my view on using Polymorphic Table Function (PTF) for dynamic pivot in Oracle 18c. Actually I was not as optimistic. The solution had at least two significant problems: the function will not see any changes on the underlying data until new hard parse of the query operating [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":1331,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[99,1,2,3,100],"tags":[102,96,103],"class_list":["post-1330","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-20c","category-general","category-oracle","category-sql","category-sql-macros","tag-20c","tag-dynamic-pivot","tag-sql-macros"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1330","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=1330"}],"version-history":[{"count":42,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1330\/revisions"}],"predecessor-version":[{"id":1455,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1330\/revisions\/1455"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media\/1331"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media?parent=1330"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/categories?post=1330"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/tags?post=1330"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}