Oracle Scratchpad

February 1, 2021

Pivot upgrade

Filed under: Oracle,Upgrades — Jonathan Lewis @ 1:34 pm GMT Feb 1,2021

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.

6 Comments »

  1. Hi Jonathan,

    From my practice, I’d say those values are typically protected with aliases:

    for    state in (
                                    'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' as a,
                                    'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB' as b,
                                    'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC' as c
                            )
    

    Since we provide the list of columns manually, it makes sense to name them with something like regular identifier on the query writing stage.

    PS I never thought of it, but when I’ve read “created using rpad(), but reported in the pivot() using the literal string value”, I actually tried to report them with function as well, to see if Oracle would evaluate it, and sure enough it does:

    for    state in (
                                    rpad('A', 35, 'A') as a ...
    

    Works as expected :)

    Comment by Viacheslav Andzhich — February 1, 2021 @ 3:02 pm GMT Feb 1,2021 | Reply

    • Viacheslav,

      Thanks for the comment.

      I think that many of the notes I’ve written could merit the response: “Surely most people would … ”
      I agree, most people, most of the time, would probably alias the longer expressions to something more suitable.

      I also used the rpad() function in the state in () list to start with, but then it was the text that appeared as the heading and failed to make the point I was trying to demonstrate. It’s funny the odd little details you discover when trying to run up a simple mode to demonstrate behaviour.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — February 1, 2021 @ 3:18 pm GMT Feb 1,2021 | Reply

  2. In my exact scenario (now that I have a moment to find it, which I didn’t at the time I wrote that comment), it was automatically generated from a min(expiration_date) expression, since I aliased the aggregate expressions… it became “‘INS_CERT_CONT’_EXPIRATION_DATE” when in 11g it was “‘INS_CERT_CONT’_EXPIRATION_DAT”.

    For Viacheslav, I think I did try aliasing the values too, when I originally wrote the query, but concluded that it was going to obfuscate more than it would clarify, for my coworkers who’ve never used PIVOT before and would have to learn about it if they ever needed to investigate this report. Since the actual literal strings being sought aren’t as long as in Jonathan’s example, aliasing both bits wouldn’t have been quite as helpful, I decided.

    But at least it’ll make you feel better that I did alias the columns in a later step. :)

    Comment by Jason Bucata — February 1, 2021 @ 3:57 pm GMT Feb 1,2021 | Reply

    • Jason,

      Thanks for the follow-up.

      I had wondered whether your problem had appeared with one of the XML methods for an open-ended list. But decided I didn’t want to model that possibility.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — February 1, 2021 @ 4:57 pm GMT Feb 1,2021 | Reply

  3. Recently encountered this plus a little twist with different values of COMPATIBLE
    https://orastory.wordpress.com/2021/01/14/compatible-identifier-intrigue/

    Comment by Dom Brooks — February 1, 2021 @ 6:25 pm GMT Feb 1,2021 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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 )

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.