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 fromp_pivot_colum DBMS_TF.COLUMNS_T– column(s) in pivot_for_clausep_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