Dynamic Pivot with SQL Macros in Oracle 20c

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 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

With Oracle 20c we now have SQL macros and I was curious, whether they can help here.

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 😉

We will be using the data from previous post for pivoting. First, we’ll create a table by doing a temporal join: Listing 1

SQL> CREATE TABLE emp_joined 
AS
SELECT * 
FROM   time_machine.temporal_join( t1 => empv
                                 , t2 => deptv
                                 , t3 =>jobv
                                 , bk_cols => columns(empno)
                                 , join_cols1 => columns(deptno, deptno)
                                 , join_cols2 => columns(jobno, jobno))
Table created.
SQL> SELECT empv_empno, empv_ename, jobv_job, valid_from, valid_to FROM emp_joined
FETCH FIRST 5 ROWS ONLY

EMPV_EMPNO EMPV_ENAME     JOBV_JOB  VALID_FROM VALID_TO 
---------- -------------- --------- ---------- ---------
      7369 SMITH          CLERK     17-DEC-80  31-DEC-89
      7499 ALLEN          SALESMAN  20-FEB-81  31-DEC-89
      7521 WARD           SALESMAN  22-FEB-81  31-DEC-89
      7566 JONES          MANAGER   02-APR-81  31-DEC-89
      7654 MARTIN         SALESMAN  28-SEP-81  31-DEC-89

5 rows selected.

Listing 1: Creating test data

Based on this table, let’s count how oft we see the jobs CLERK and ANALYST. The SQL query to do this with PIVOT operator introduced in 11g:

SQL> SELECT * 
FROM   (SELECT empv_empno, jobv_job 
        FROM   emp_joined)
PIVOT  ( 
  COUNT( DISTINCT empv_empno)  
  FOR (jobv_job) IN ( 'CLERK' clerk, 'ANALYST' analyst )
)

     CLERK    ANALYST
---------- ----------
         4          2
1 row selected.

Listing 2: Static query using PIVOT operator

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’s why everyone is so keen on dynamic pivot. Wouldn’t it be nice to just call a SQL macro function which returns these columns instead of explicitly list them? Something like this:

SQL> SELECT * 
FROM   (SELECT empv_empno, jobv_job 
        FROM   emp_joined)
PIVOT  ( 
  COUNT( DISTINCT empv_empno)  
  FOR (jobv_job) IN ( get_columns_macro() )
)

Listing 3: This would not work

We can try to develop a macro function. It is quite trivial, but calling the function this way will not work:

SQL> CREATE FUNCTION get_pivot_in_clause (p_tab DBMS_TF.TABLE_T
                                        , p_pivot_column DBMS_TF.COLUMNS_T
                                        )
RETURN VARCHAR2 SQL_MACRO
IS
v_sql varchar2(1000);
BEGIN
  v_sql := q'!select listagg(distinct upper(''''||%COL_PLACEHOLDER%||''' '||%COL_PLACEHOLDER% ),',') pivot_in_clause from p_tab!'; 
  return replace (v_sql, '%COL_PLACEHOLDER%', replace(p_pivot_column(1),'"'))||p_tab.table_name;
END;
Function created.

SQL> select * from get_pivot_in_clause (emp_joined, columns(jobv_job))

PIVOT_IN_CLAUSE                                                                 
--------------------------------------------------------------------------------
'CLERK' CLERK,'SALESMAN' SALESMAN,'MANAGER' MANAGER,'ANALYST' ANALYST,'PRESIDENT
' PRESIDENT                                                                     
                                                                                
1 row selected.


SELECT * 
FROM   (SELECT empv_empno, jobv_job 
        FROM   emp_joined)
PIVOT  ( 
  COUNT( DISTINCT empv_empno)  
  FOR (jobv_job) IN ( get_pivot_in_clause(emp_joined, columns(jobv_job) ) )
)
                         *
Error at line 6
ORA-56901: non-constant expression is not allowed for pivot|unpivot values

Listing 5: SQL macro works stand-alone, but calling in PIVOT fails

This doesn’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’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’t work even if Oracle would not throw ORA-56901 is that you only can call table macros in FROM clause.

The next try is to provide a table macro which returns the whole pivot query. Let’s start with minimalist requirements and simply reproduce the query from Listing 1 looking at the syntax diagram for PIVOT operator.

For simplicity we suppose we only want one known aggregate function (COUNT) on one column and we also have just one pivot column. Let’s look at the implementation in Listing 6.

We need following parameter:

  • p_tab DBMS_TF.TABLE_T – the table to select from
  • p_pivot_colum DBMS_TF.COLUMNS_T – column(s) in pivot_for_clause
  • p_agg_cols DBMS_TF.COLUMNS_T – columns(s), aggregate function operates on

Lines 10-12 : execute the SQM function we created earlier to get the pivot_in_clause
Line 13-17: build the PIVOT statement and return the string

SQL> CREATE OR REPLACE FUNCTION dynamic_pivot (p_tab DBMS_TF.TABLE_T
                                        , p_pivot_column DBMS_TF.COLUMNS_T
                                        , p_agg_cols DBMS_TF.COLUMNS_T
                                        )
RETURN VARCHAR2 SQL_MACRO
IS
v_cols varchar2(2000);
v_sql varchar2(500);
BEGIN
  v_sql := 'select pivot_in_clause from get_pivot_in_clause('
          ||p_tab.table_name||', columns('||p_pivot_column(1)||') ) '; 
  EXECUTE IMMEDIATE v_sql INTO v_cols;
  v_sql :=  'select * from (select '||p_pivot_column(1)||
               ', '||p_agg_cols(1)||' from p_tab)
         pivot  ( count(distinct '||p_agg_cols(1)|| ')'
         ||' for ('||p_pivot_column(1)||') in (  '||v_cols||' ) )';
  RETURN v_sql;         
END;
Function created.

SQL> SELECT * 
FROM   dynamic_pivot(emp_joined
                   , COLUMNS(JOBV_JOB)
                   , COLUMNS(empv_empno)
                   )

     CLERK   SALESMAN    MANAGER    ANALYST  PRESIDENT
---------- ---------- ---------- ---------- ----------
         4          4          3          2          1
1 row selected.

SQL> UPDATE emp_joined SET jobv_job = 'DBA' 
WHERE  empv_empvid = 1
1 row updated.

SQL> -- Where are my DBA's?
SQL> SELECT * 
FROM   dynamic_pivot(emp_joined
                   , COLUMNS(JOBV_JOB)
                   , COLUMNS(empv_empno)
                   )

     CLERK   SALESMAN    MANAGER    ANALYST  PRESIDENT
---------- ---------- ---------- ---------- ----------
         4          4          3          2          1
1 row selected.

SQL> -- Purge the SQL out if shared pool
SQL> SELECT address, hash_value FROM   V$SQLAREA ...

ADDRESS          HASH_VALUE
---------------- ----------
00000001545B6C40 3320021849
1 row selected.

SQL> exec sys.DBMS_SHARED_POOL.PURGE ('00000001545B6C40,	3320021849', 'C')
 PL/SQL procedure successfully completed.

SQL> -- After hard parse we will see DBA:
SQL> SELECT * 
FROM   dynamic_pivot(emp_joined
                   , COLUMNS(JOBV_JOB)
                   , COLUMNS(empv_empno)
                   )

       DBA   SALESMAN    MANAGER    ANALYST  PRESIDENT      CLERK
---------- ---------- ---------- ---------- ---------- ----------
         1          4          3          2          1          4
1 row selected.

SQL> ROLLBACK
Rollback complete.

SQL> -- There are no DBA's now, but we still see the column!
SQL> SELECT * 
FROM   dynamic_pivot(emp_joined
                   , COLUMNS(JOBV_JOB)
                   , COLUMNS(empv_empno)
                   )

       DBA   SALESMAN    MANAGER    ANALYST  PRESIDENT      CLERK
---------- ---------- ---------- ---------- ---------- ----------
         0          4          3          2          1          4
1 row selected.

SQL> -- It disappears after hard parse
SQL> exec sys.DBMS_SHARED_POOL.PURGE ('00000001545B6C40,	3320021849', 'C')
 PL/SQL procedure successfully completed.

SQL> SELECT * 
FROM   dynamic_pivot(emp_joined
                   , COLUMNS(JOBV_JOB)
                   , COLUMNS(empv_empno)
                   )

     CLERK   SALESMAN    MANAGER    ANALYST  PRESIDENT
---------- ---------- ---------- ---------- ----------
         4          4          3          2          1
1 row selected.

Listing 6: SQL macro works, but it is “DML-agnostic”

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.

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 (p_tab.table_name) into the statement to get column names. That’s because inside the SQL macro no parameter substitution takes place. It’s only the case on exit of the SQM function. Just like in line 14, we are referencing p_tab 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 p_tab.table_name is NULL.

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’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.

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.

SQL> CREATE OR REPLACE FUNCTION dynamic_pivot (p_tab DBMS_TF.TABLE_T
                                        , p_pivot_column DBMS_TF.COLUMNS_T
                                        , p_agg_cols DBMS_TF.COLUMNS_T
                                        , p_agg_func sys.ODCIVarchar2List)
return varchar2 sql_macro
is
v_cols varchar2(2000);
v_sql varchar2(500);
v_agg varchar2(2000);
v_agg_cols varchar2(2000);
begin
  v_sql := 'select pivot_in_clause from get_pivot_in_clause('||p_tab.table_name||', columns('||p_pivot_column(1)||') ) '; 
  EXECUTE IMMEDIATE v_sql INTO v_cols;
  for i in 1..p_agg_func.count loop
    v_agg := v_agg||','||p_agg_func(i); 
  end loop;
  v_agg := ltrim(v_agg, ',');
  for i in 1..p_agg_cols.count loop
    v_agg_cols := v_agg_cols||','||p_agg_cols(i); 
  end loop;
  v_agg := ltrim(v_agg, ',');
  v_agg_cols := ltrim(v_agg_cols, ',');    
  v_sql :=  'select * from (select '||p_pivot_column(1)||', '
                                    ||v_agg_cols||' from p_tab) '
         ||'  pivot  ( '||v_agg 
         ||' for ('||p_pivot_column(1)||') in (  '||v_cols||' ) )';
  RETURN v_sql;         
end;
Function created.

SQL> create or replace view emp_joined_big as
select e.* 
from emp_joined e 
     cross join 
     (select level from dual connect by level<=100)
View created.

SQL> select * from  dynamic_pivot(emp_joined_big
                           , columns(JOBV_JOB)
                           , columns(empv_empno, empv_sal)
                           , sys.ODCIVarchar2List('count( empv_empno) as cnt'
                                                , 'sum(empv_sal) as sal'))

 CLERK_CNT  CLERK_SAL SALESMAN_CNT SALESMAN_SAL MANAGER_CNT MANAGER_SAL ANALYST_CNT ANALYST_SAL PRESIDENT_CNT PRESIDENT_SAL
---------- ---------- ------------ ------------ ----------- ----------- ----------- ----------- ------------- -------------
       800     830000          800      1120000         600     1655000         400     1200000           200       1000000
1 row selected.

Listing 7: extended SQL macro

Summary

The implementation for dynamic PIVOT with SQL macros is much simpler as with PTF. But it also has limitations: it doesn’t work with subqueries and doesn’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.

Nevertheless, there are two reasonable enhancement requests @Oracle:

  • Please make it possible to pass table parameter on to other SQL macro functions called inside a macro
  • 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

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

*