SQL table macros 11: JUST_PIVOT for 21c (and 19c)

[HUGE UPDATE 2022-01-14 15:45 GMT: thanks to Iudith Mentzel, here is a much simpler and better solution than the one I originally posted today!]

[UPDATE 2022-01-15: to ensure that the INVALIDATE_OBJECT function is called only once, I made it DETERMINISTIC; I also wrapped the call in a scalar subquery, as Iudith Mentzel suggested in her comment below.]

My previous JUST_PIVOT solution for dynamic pivoting does not work in Oracle DB 21c: sorry about that!

Here’s the explanation:

1. When we want to pivot rows to columns, the columns may change if the data changes.
2. A SQL macro can change the columns, but only during the hard parse phase.
3. My previous attempt forces a hard parse every time by using a “flashback query”:
   • All parsed statements cause a “cursor” to be placed in the shared pool;
   • cursors that can be “shared” are reused if the same statement is submitted again;
   • cursors from flashback queries are marked “unshareable”, which means the cursor cannot be be reused, so there is a hard parse at every execution.
4. Starting with version 21c, flashback query cursors can be shared, which means that technique no longer works.

Is this a bug or a feature?

Why, after all these years, have flashback queries become shareable? Was this really a conscious decision by Oracle, or is it a bug that Oracle will “fix” and Make my Macro Great Again? Thanks to a tweet by Martin Berger (@martinberx) which got me to read this article by Tim Hall , I confirmed that this new behavior is intended:

select description from V$SYSTEM_FIX_CONTROL
where bugno = 10123661;

DESCRIPTION
--------------
Enable cursor sharing for AS OF queries

 

Alternative solution

This is based on a brilliant suggestion by Iudith Mentzel on Twitter (@mentzel_iudith):

• Create a function that invalidates the SQL macro using DBMS_UTILITY.INVALIDATE;
• Have the generated SQL statement call that function.
  • The macro will have finished, so the invalidation does not interfere with the macro execution;
  • The invalidation does not commit, so no bad side effects.
  • Also no child cursors! That was the drawback of the AS OF technique.

create or replace function invalidate_object (
  p_owner in varchar2,
  p_object_name in varchar2
) return number deterministic is
  l_object_id number;
begin
  select object_id into l_object_id
  from all_objects
  where (owner, object_name) = ((p_owner, p_object_name));
  dbms_utility.invalidate(
    p_object_id => l_object_id,
    p_option_flags => dbms_utility.INV_ERROR_ON_RESTRICTIONS
  );
  return 0;
end invalidate_object;
/

create or replace function just_pivot(
  p_INPUT_QUERY in sys.odcivarchar2list,
  p_GROUP_COLS in dbms_tf.columns_t,
  p_PIVOT_COLS in dbms_tf.columns_t,
  p_AGG_COLS in dbms_tf.columns_t,
  p_total_label in dbms_tf.columns_t default null   
) return clob sql_macro is

/* Just Pivot MACRO
This macro takes as input a query that does an explicit GROUP BY.
The macro first uses the input query to get the list of pivot columns,
then wraps the input query in a final query that does a PIVOT using MAX().

Input:
1) p_INPUT_QUERY: the input query must be a literal
   wrapped in a sys.odcivarchar2list() collection.
   This allows the content to be accessed by the macro
   (macros do not have access to the content of VARCHAR2 literals).

Every column in the input query must also appear in one of these column lists:
2) p_GROUP_COLS: columns that are GROUPed BY explicitly in the input query
   and implicitly in the PIVOT
3) p_PIVOT_COLS: column that are GROUPed BY explicitly in the input query
   and pivoted in the PIVOT
4) p_AGG_COLS: column that are aggregated explicitly in the input query
   and "re-aggregated" using MAX() in the PIVOT
5) p_total_label: label to use for pivot totals (if CUBE, ROLLUP, etc.)
   null values are assumed to be totals and will be replaced with the label
   ('Total' is the default) and will be ordered at the end

Processing:
- within the macro, execute the input query to get the list of pivot columns
- generate the final SQL statement using input query and intermediate result
- the final SQL statement will call a function to invalidate this function,
  which will invalidate the cursor and cause the query to be parsed
  at every execution.
*/

/*
- PIVOT_IN_LIST concatenates values from all p_PIVOT_COLS columns
  for example, if (DNAME,JOB) then
    ('ACCOUNTING','CLERK') as "ACCOUNTING_CLERK",
    ('ACCOUNTING','MANAGER') as "ACCOUNTING_MANAGER",
  and so on
*/
  l_pivot_in_list_sql long :=  
q'@select listagg('(' || EXPR || ') as "' || AL || '"', ',
') within group(order by #PIVOT_COLS#)
from (
  select distinct
  #EXPR# EXPR,
  #ALIAS# AL,
  #PIVOT_COLS#
  from (#INPUT_QUERY#)
)@';
  l_PIVOT_COLS varchar2(4000);
  l_EXPR varchar2(4000);
  l_ALIAS varchar2(4000);

  l_final_sql long :=
'select * from (
  select #NVL_COLS#,
  #AGG_COLS#
  from (#INPUT_QUERY#)
  where 0 = (
    select invalidate_object(
      '''||$$PLSQL_UNIT_OWNER||''','''||$$PLSQL_UNIT||'''
    ) from dual
  )
)
pivot(#AGG_MAX_COLS# for (#PIVOT_COLS#) in (
  #PIVOT_IN_LIST#
))
order by #ORDER_COLS#';
  l_NVL_COLS varchar2(4000);
  l_AGG_COLS varchar2(4000);
  l_AGG_MAX_COLS varchar2(4000);
  l_PIVOT_IN_LIST varchar2(4000);
  l_ORDER_COLS varchar2(4000);
  
  l_total_label varchar2(32) := 'Total';

begin

  -- set value of l_total_label, which is needed right away
  if p_total_label is not null then
    sqm_util.list_columns(p_total_label, l_total_label, '%s', null, true);
  end if;
  
  -- set values to be plugged into l_pivot_in_list_sql
  sqm_util.list_columns(p_PIVOT_COLS, l_PIVOT_COLS);
  sqm_util.list_columns(
    p_PIVOT_COLS, l_EXPR,
    '''''''''||nvl(%s||null,'''||l_total_label||''')||''''''''', 
    q'§||','||§',
    true
  );
  sqm_util.list_columns(p_PIVOT_COLS, l_ALIAS,
    'nvl(%s||null,'''||l_total_label||''')',
    q'§||'_'||§',
    true
  );
  
  -- plug values into l_pivot_in_list_sql
  l_pivot_in_list_sql := replace(replace(replace(replace(
    l_pivot_in_list_sql,
    '#PIVOT_COLS#', l_PIVOT_COLS),
    '#EXPR#', l_EXPR),
    '#ALIAS#', l_ALIAS),
    '#INPUT_QUERY#', p_INPUT_QUERY(1)
  );  
  -- dbms_output.put_line(l_pivot_in_list_sql);
  
  -- execute l_pivot_in_list_sql
  execute immediate l_pivot_in_list_sql into l_PIVOT_IN_LIST;
  -- dbms_output.put_line(l_PIVOT_IN_LIST);
  
  -- set values to be plugged into l_final_sql
  sqm_util.list_columns(
    p_GROUP_COLS multiset union p_PIVOT_COLS,
    l_NVL_COLS,
    'nvl(%s||null, '''||l_total_label||''') %s'
  );

  sqm_util.list_columns(p_AGG_COLS, l_AGG_MAX_COLS, 'max(%s) %s');
  sqm_util.list_columns(p_AGG_COLS, l_AGG_COLS);
  sqm_util.list_columns(p_GROUP_COLS, l_ORDER_COLS,
    'nullif(%s, '''||l_total_label||''')'
  );
  
  -- plug values into l_final_sql
  l_final_sql := replace(replace(replace(replace(replace(replace(replace(
    l_final_sql,
    '#NVL_COLS#', l_NVL_COLS),
    '#AGG_COLS#', l_AGG_COLS),
    '#INPUT_QUERY#', p_INPUT_QUERY(1)),
    '#AGG_MAX_COLS#', l_AGG_MAX_COLS),
    '#PIVOT_COLS#', l_PIVOT_COLS),
    '#ORDER_COLS#', l_ORDER_COLS),
    '#PIVOT_IN_LIST#', l_PIVOT_IN_LIST);
  
  dbms_output.put_line(l_final_sql);
  return l_final_sql;
  
end just_pivot;
/

Posts on SQL table macros

• SQL table macros 1: a moving target?
• SQL table macros 2: “polymorphic” is not “dynamic”
• SQL table macros 3: parameter strangeness
• SQL table macros 4: distribute into equal parts
• SQL table macros 5: dynamic but dangerous!
• SQL table macros 6: SQM_UTIL package
• SQL table macros 7: Select excluding
• SQL table macros 8: Print table
• SQL table macros 9: just pivot!
• SQL table macros 10: JUST_PIVOT examples
• SQL table macros 11: JUST_PIVOT for 21c (and 19c)
• SQL table macros 12: compare tables or queries
• SQL table macros 13: compare with primary key

Advertisement