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;

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;
  select object_id into l_object_id
  from all_objects
  where (owner, object_name) = ((p_owner, p_object_name));
    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().

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

- 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
  and so on
  l_pivot_in_list_sql long :=  
q'@select listagg('(' || EXPR || ') as "' || AL || '"', ',
') within group(order by #PIVOT_COLS#)
from (
  select distinct
  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#,
  from (#INPUT_QUERY#)
  where 0 = (
    select invalidate_object(
    ) from dual
pivot(#AGG_MAX_COLS# for (#PIVOT_COLS#) in (
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';


  -- 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_ALIAS,
  -- plug values into l_pivot_in_list_sql
  l_pivot_in_list_sql := replace(replace(replace(replace(
    '#EXPR#', l_EXPR),
    '#ALIAS#', l_ALIAS),
  -- 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
    p_GROUP_COLS multiset union p_PIVOT_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(
    '#NVL_COLS#', l_NVL_COLS),
    '#AGG_COLS#', l_AGG_COLS),
  return l_final_sql;
end just_pivot;

8 thoughts on “SQL table macros 11: JUST_PIVOT for 21c (and 19c)

  1. Hello Stew,

    Thanks a lot for adopting my solution :)

    If you don’t mind, I would be extremely glad to also see your original workaround of today to the flashback query 21c problem :)
    I am sure that we all can learn A LOT from any such attempt to solve a problem.

    Just a short remark regarding the new workaround :

    I see that you did not use a scalar query from DUAL for calling the INVALIDATE_OBJECT function.

    I just checked that, indeed, if I do the same simplification in my example, the function is still being called only once.

    This is logical, because the arguments passed to the function are literal values.

    But, as we know, it is generally stated that “we don’t have control on the number of times that a pl/sql function is called during a SQL statement execution”, so, this is why I wrapped the function call into a scalar query, as usually advised by Tom Kyte.

    Maybe this is not necessary, but can be done just as a “safety measure” against possible changes in various versions
    and/or more complicated query cases, where the behavior might be different.

    Thanks a lot & Best Regards,
    Iudith Mentzel

  2. Hi Iudith,

    I have spent too much time on failed or suboptimal attempts already. I don’t feel like writing about them. It might only lead to “cognitive load” anyway.

    I just implemented the safety measure you rightly suggested, thanks.

    Best regards,

  3. Hello Stew,

    I would be very grateful if you could test and confirm that the new solution does not cause concurrency issues,
    in the sense that, if one session executes a query that takes a relatively long time,
    then another session issuing a query based on the same SQL MACRO will NOT have to wait until the first session
    finishes running, for being able to start its own parsing phase, which includes a recompilation of the (now invalid) SQL MACRO.

    Unfortunately, I cannot fully check concurrency issues using LiveSQL …

    But, in case that this is a problem, there exists an easy work-around for it.

    Thanks a lot & Best Regards,
    Iudith Mentzel

    • Iudith,

      I tested that before posting: one session fetching only part of the query, another session able to query before the first session finishes fetching. I’m not sure that completely covers concurrency issues; however, a solution that forces a hard parse at every execution should not be used in a database with many concurrent users anyway!

      Best regards,

  4. Hello Stew,
    You are right regarding hard parses in a highly concurrent environment, but, a PIVOT query usually has no way around this anyway,
    if you want it to return “fresh” data.

    For a full concurrency test, I think that there is a need to “inflate” the query running time, for example by including a
    SLEEP function, because the DDL lock, if held at all beyond the parsing phase, will have probably been released when starting to fetch

    Just another remark, regarding your blog:

    For some reason, I only receive notifications about new posts that you are publishing, but not about new comments to previous posts,
    as I use to receive for other blogs, including other wordpress blogs.
    This is usually the reason for my delayed reactions :(

    Thanks a lot & Best Regards,

    • Iudith,

      Where do you think the SLEEP call should go?

      To allow you to subscribe to comments, I would have to install a plugin. Free wordpress.com blogs like mine cannot use plugins.

      Best regards,

  5. Hi Stew,

    Regarding the Flashback query cursors being shareable, Oracle’s Flashback tools – Queries (FQ) and Data Archive (FDA) had an historic habit of flooding the shared pool, because many background queries weren’t parameterised or not shareable. On production systems I work on, which use FDA and FQ, we’d see hundreds / thousands of them in the shared pool – not ideal as it was aging out others. Several bugs were raised over the past few years regarding that and it seems Oracle have done a lot of work in that area to address it. I suspect FQs non shareable ones were included in that round of bug squashes.


    • Hi Paul,

      Given that the bug is referenced in V$SYSTEM_FIX_CONTROL (see above), I would say it is more a certainty than a “suspicion” ;-)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s