Pivot upgrade

I’ve hardly ever touched the pivot/unpivot feature in SQL, but a recent comment by Jason Bucata on a note I’d written about Java names and the effects of newer versions of Oracle allowing longer object and column names prompted me to look at a script I wrote several years ago for 11g.

As Jason pointed out, it’s another case where the output from a script might suffer some cosmetic changes because of an upgrade. Here’s the script to generate some data and run a query:

rem
rem     Script:         pivot_upgrade.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Feb 2021
rem
rem     Last tested 
rem             19,3,0,0
rem             12.2.0.1
rem             11.2.0.4
rem

set linesize 144
set trimspool on

create table t1
as
with generator as (
        select  --+ materialize
                rownum id 
        from dual 
        connect by 
                rownum <= 10000
)
select
        rownum                  id,
        rownum                  n1,
        rpad(chr(65 + mod(rownum,3)), 35, 
             chr(65 + mod(rownum,3))
        )                       state,
        mod(rownum,4)           flag,
        lpad(rownum,10,'0')     small_vc
from
        generator       v1,
        generator       v2
where
        rownum <= 10000
/

select  *
from
        (
        select
                flag, state, n1
        from
                t1
        )       piv
        pivot   (
                        avg(n1)
                 for    state in (
                                'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA',
                                'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB',
                                'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'
                        )
        )
order by
        flag
;

I’ve hijacked (cloned and hacked) a script I wrote for another little test so don’t read too much into the data that I’ve created and how I’ve created it. All that matters is that I have a column with three distinct values and I want a report that summarises the data across the page according to the value of those three columns.

To be awkward (and demonstrate the point of the blog note), the values in the columns are all 35 character strings – created using rpad(), but reported in the pivot() using the literal string value.

Here’s the result of the query from 12c (in my case 12.2.0.1) onwards:

      FLAG 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB' 'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'
---------- ------------------------------------- ------------------------------------- -------------------------------------
         0                                  5004                                  5002                                  5000
         1                                  5001                                  4999                                  4997
         2                                  4998                                  5002                                  5000
         3                                  5001                                  4999                                  5003

You’ll notice that the pivoted column heading include the single-quote marks, plus the 35 defining characters. Compare this with the result from 11.2.0.4:

      FLAG 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAA 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBB 'CCCCCCCCCCCCCCCCCCCCCCCCCCCCC
---------- ------------------------------ ------------------------------ ------------------------------
         0                           5004                           5002                           5000
         1                           5001                           4999                           4997
         2                           4998                           5002                           5000
         3                           5001                           4999                           5003

Including the initial single-quote mark the headings are exactly 30 characters long – the historical limit under Oracle’s naming conventions.

So if you’re still using 11g, an upgrade to a more recent version of Oracle could end up forcing you to do a few little adjustments to some of your code simply to ensure column lengths (and subsequent line lengths) don’t change.