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.
OraStory 
Recent Comments