COMPATIBLE Identifier Intrigue

An observation about a crucial difference whilst testing an upgrade with / without COMPATIBLE.

Upgrade from 11.2.0.4 to 19.6
Initial upgrade testing done with default COMPATIBLE of 19.0.0
Another enviroment was upgraded with COMPATIBLE set to 11.2.0.4.0, rationale being that it allowed for “easy” downgrade should we run into unresolvable issues.

For background reason, please see Mike Dietrich blog on COMPATIBLE: https://mikedietrichde.com/2019/04/17/when-and-how-should-you-change-compatible/
I am not criticising this article at all.
There is an example in the blog about identifier being such a feature impacted by COMPATIBLE.And there are some interesting points in the discussions about the impact of testing.

We had an issue with a view on top of a PIVOT query.
PIVOT is slightly unusual in that the resulting column names are dynamic.

This example is a bit ridiculous… however… Let’s say we had a pivot a bit like this:

select owner, created_year, "January can be a cold month _T"
  from (
         select owner
              , to_number(to_char(created,'YYYY')) created_year
              , to_char(created,'MON') created_month
              , object_type
           from dba_objects
           where owner in ('SYSTEM','SYS')
       )
pivot (
         max(object_type) as type, count(*) as cnt for created_month in
         ('JAN' as "January can be a cold month "
         ,'FEB' as "February can be a bit miser"
         ,'MAR' as "Mad as a Hare"
         ,'APR' as "Showers"
         ,'MAY' as "Can be magnificent"
         ,'JUN' as "Flamin'"
         ,'JUL' as "Peak Summer"
         ,'AUG' as "Often disappointing"
         ,'SEP' as "Always a bonus"
         ,'OCT' AS "Neither here nor there"
         ,'NOV' as "All the best babies born he"
         ,'DEC' as "Christmas? Already?")
       )
order
    by owner
     , created_year;
OWNER                          CREATED_YEAR January can be a co
------------------------------ ------------ -------------------
SYS                                    2014
SYS                                    2015
SYS                                    2016 DIRECTORY
SYS                                    2017
SYS                                    2018 TABLE SUBPARTITION
SYS                                    2019
SYS                                    2020
SYS                                    2021 TABLE PARTITION
SYSTEM                                 2014                    

9 rows selected.

You can see our final column selection (which might be a view definition in the real world ?!?!??!), is relying on the 11g implicit identifier truncation to 30 characters of “January can be a cold month _T”.
Unwise… in hindsight.

So we fix this and release it to our default 19.6 COMPATIBLE database.

select owner, created_year, "January can be a cold month _TYPE"
  from (
         select owner
              , to_number(to_char(created,'YYYY')) created_year
              , to_char(created,'MON') created_month
              , object_type
           from dba_objects
           where owner in ('SYSTEM','SYS')
       )
pivot (
         max(object_type) as type, count(*) as cnt for created_month in
         ('JAN' as "January can be a cold month "
         ,'FEB' as "February can be a bit miser"
         ,'MAR' as "Mad as a Hare"
         ,'APR' as "Showers"
         ,'MAY' as "Can be magnificent"
         ,'JUN' as "Flamin'"
         ,'JUL' as "Peak Summer"
         ,'AUG' as "Often disappointing"
         ,'SEP' as "Always a bonus"
         ,'OCT' AS "Neither here nor there"
         ,'NOV' as "All the best babies born he"
         ,'DEC' as "Christmas? Already?")
       )
order
    by owner
     , created_year;
	 OWNER      CREATED_YEAR January can be a cold m
---------- ------------ -----------------------
SYS                2015
SYS                2016 DIRECTORY
SYS                2017
SYS                2018
SYS                2019
SYS                2020
SYS                2021 VIEW
SYSTEM             2015
SYSTEM             2021 VIEW                   

9 rows selected.

And then we release it to the other compatible = 11.2.0.4 19c database. Well, I’m sure that there aren’t going to be any surprises that it doesn’t bloody work:

select owner, created_year, "January can be a cold month _TYPE"
  from (
         select owner
              , to_number(to_char(created,'YYYY')) created_year
              , to_char(created,'MON') created_month
              , object_type
           from dba_objects
           where owner in ('SYSTEM','SYS')
       )
pivot (
         max(object_type) as type, count(*) as cnt for created_month in
         ('JAN' as "January can be a cold month "
         ,'FEB' as "February can be a bit miser"
         ,'MAR' as "Mad as a Hare"
         ,'APR' as "Showers"
         ,'MAY' as "Can be magnificent"
         ,'JUN' as "Flamin'"
         ,'JUL' as "Peak Summer"
         ,'AUG' as "Often disappointing"
         ,'SEP' as "Always a bonus"
         ,'OCT' AS "Neither here nor there"
         ,'NOV' as "All the best babies born he"
         ,'DEC' as "Christmas? Already?")
       )
order
    by owner
     , created_year;
ORA-00972: identifier is too long
00972. 00000 -  "identifier is too long"
*Cause:    An identifier with more than 30 characters was specified.
*Action:   Specify at most 30 characters.
Error at Line: 128 Column: 29

So what?
We just use the old one right?
No – it doesn’t bloody work either:

select owner, created_year, "January can be a cold month _T"
  from (
         select owner
              , to_number(to_char(created,'YYYY')) created_year
              , to_char(created,'MON') created_month
              , object_type
           from dba_objects
           where owner in ('SYSTEM','SYS')
       )
pivot (
         max(object_type) as type, count(*) as cnt for created_month in
         ('JAN' as "January can be a cold month "
         ,'FEB' as "February can be a bit miser"
         ,'MAR' as "Mad as a Hare"
         ,'APR' as "Showers"
         ,'MAY' as "Can be magnificent"
         ,'JUN' as "Flamin'"
         ,'JUL' as "Peak Summer"
         ,'AUG' as "Often disappointing"
         ,'SEP' as "Always a bonus"
         ,'OCT' AS "Neither here nor there"
         ,'NOV' as "All the best babies born he"
         ,'DEC' as "Christmas? Already?")
       )
order
    by owner
     , created_year;
ORA-00904: "January can be a cold month _T": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 100 Column: 29

Fair enough… What should it be then Mr Compatible?
Give us a clue:

select *
  from (
         select owner
              , to_number(to_char(created,'YYYY')) created_year
              , to_char(created,'MON') created_month
              , object_type
           from dba_objects
           where owner in ('SYSTEM','SYS')
       )
pivot (
         max(object_type) as type, count(*) as cnt for created_month in
         ('JAN' as "January can be a cold month "
         ,'FEB' as "February can be a bit miser"
         ,'MAR' as "Mad as a Hare"
         ,'APR' as "Showers"
         ,'MAY' as "Can be magnificent"
         ,'JUN' as "Flamin'"
         ,'JUL' as "Peak Summer"
         ,'AUG' as "Often disappointing"
         ,'SEP' as "Always a bonus"
         ,'OCT' AS "Neither here nor there"
         ,'NOV' as "All the best babies born he"
         ,'DEC' as "Christmas? Already?")
       )
order
    by owner
     , created_year;
OWNER      CREATED_YEAR January can be a cold m
---------- ------------ -----------------------
SYS                2015
SYS                2016 DIRECTORY
SYS                2017
SYS                2018
SYS                2019
SYS                2020 TABLE PARTITION
SYS                2021 VIEW
SYSTEM             2015
SYSTEM             2021 VIEW

Ok. Give me a bit more of a clue please:

create table dom1 as
select *
  from (
         select owner
              , to_number(to_char(created,'YYYY')) created_year
              , to_char(created,'MON') created_month
              , object_type
           from all_objects
           where owner in ('SYSTEM','SYS')
       )
pivot (
         max(object_type) as type for created_month in
         ('JAN' as "January can be a cold month ")
       )
order
    by owner
     , created_year;

Table DOM1 created.
select * from dom1;
OWNER      CREATED_YEAR January can be a cold m
---------- ------------ -----------------------
SYS                2015
SYS                2016
SYS                2017
SYS                2018
SYS                2019
SYS                2021 VIEW
SYSTEM             2015
SYSTEM             2021 VIEW

You’re playing hard to get…

select listagg('"'||column_name||'"',',') within group(order by column_id) from user_tab_columns where table_name = 'DOM1';
LISTAGG('"'||COLUMN_NAME||'"',',')WITHINGROUP(ORDERBYCOLUMN_ID)
-----------------------------------------------------------------
"OWNER","CREATED_YEAR","January can be a cold month _TYPE"
select "OWNER","CREATED_YEAR","January can be a cold month _TYPE" from dom1;
ORA-00972: identifier is too long
00972. 00000 -  "identifier is too long"
*Cause:    An identifier with more than 30 characters was specified.
*Action:   Specify at most 30 characters.
Error at Line: 225 Column: 36</code></pre>
select "OWNER","CREATED_YEAR","January can be a cold month _TYPE"
from (select *
  from (
         select owner
              , to_number(to_char(created,'YYYY')) created_year
              , to_char(created,'MON') created_month
              , object_type
           from all_objects
           where owner in ('SYSTEM','SYS')
       )
pivot (
         max(object_type) as type for created_month in
         ('JAN' as "January can be a cold month ")
       )
order
    by owner;
ORA-00972: identifier is too long
00972. 00000 -  "identifier is too long"
*Cause:    An identifier with more than 30 characters was specified.
*Action:   Specify at most 30 characters.
Error at Line: 227 Column: 31

OK… enough already… I’m not playing these games 🙂

The obvious solution is not to go anywhere near the limit in the first place. This was just a bit of messing around.

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 )

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

<span>%d</span> bloggers like this: