Ora-00904: Invalid Identifier After Upgrading From 10.2.0.4 to 11.2.0.2 (Doc ID 1271442.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.

Symptoms

On 11.2.0.2, encountered  ORA-00904: "S"."END_DATE": invalid identifier when running this query:

MERGE INTO station s
USING station_load sl
ON (s.headend_controller_sid = (SELECT headend_controller_sid FROM headend_controller
WHERE headend_controller.controller_id = sl.controller_id)
AND s.channel_map_id = sl.channel_map_id
AND s.channel_code = sl.channel_code
AND (sl.call_sign IS NULL OR s.call_sign = sl.call_sign)
AND s.channel_name = sl.channel_name
AND s.end_date > SYSDATE)
WHEN NOT MATCHED THEN
INSERT (
station_sid,
headend_controller_sid,
channel_map_id,
channel_code,
call_sign,
channel_name,
start_date,
end_date,
created_data_load_sid,
modified_data_load_sid
)
VALUES (
seq_station_sid.NEXTVAL,
(SELECT headend_controller_sid FROM headend_controller
WHERE headend_controller.controller_id = to_char(sl.controller_id)),
sl.channel_map_id,
sl.channel_code,
NVL(sl.call_sign, sl.channel_name), sl.channel_name,
SYSDATE,
TO_DATE('12/31/3999 23:59:59','MM/DD/YYYY HH24:MI:SS'),
8,
8)
;


Setting these parameters does not resolve the issue:

star_transformation_enabled=true
_optimizer_disable_strans_sanity_checks=63
_always_star_transformation=true
optimizer_features_enable = '11.1.0.7
'


Call stack
skdstdst  ksedst1  ksedst  dbkedDefDump  ksedmp dbkdaKsdActDriver  dbgdaExecuteAction  dbgdaRunAction  dbgdRunActions  dbgdProcessEventActions  dbgdChkEventKgErr  dbkdChkEventRdbmsEr  PGOSF13_ksfpec  dbgePostErrorKGE       1122  dbkePostKGE_kgsf  kgesev  kgesec1  qcuErrGen  qcsRslvName  qcsridn  qcsraic  qcspqbDescendents  qcspqb   kkmdrv  opiSem  opiDeferredSem  opitca  PGOSF634_kksFullTypeCheck  rpiswu2  ypeCheck  kksLoadChild  kxsGetRuntimeLock kksfbc  kkspsc0  kksParseCursor  opiosq0  opiall0  opikpr  opiodr  PGOSF175_rpidrus  skgmstack  rpiswu2 kprball  kkmupspre  kkmpre  opiSem  opiDeferredSem  opitca  PGOSF634_kksFullT  ypeCheck  rpiswu2  ypeCheck kksLoadChild  kxsGetRuntimeLock  kksfbc  kkspsc0  kksParseCursor  opiosq0  kpooprx  kpoal8  opiodr  ttcpip opitsk  opiino  opiodr  opidrv  sou2o  opimai_real  ssthrdmain  main  libc_start_main  start

Changes

Database was upgraded from 10.2.0.4 to 11.2.0.2
Issue did not occur in 10.2.0.4

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms