ORA-900 Caused By Update Statement With Unian All View Over Dblink
(Doc ID 2697013.1)
Last updated on SEPTEMBER 23, 2020
Applies to:
Oracle Database - Standard Edition - Version 19.3.0.0.0 to 19.8.0.0.0 [Release 19]Information in this document applies to any platform.
Symptoms
- ORA-00900 error is caused by update statement wi<ALIAS0> views over DBLINK.
SQL> UPDATE <TABLE_NAME1> <ALIAS0>
SET ( <COL1> )
= ( SELECT SUM( <ALIAS>.<COL2> )
FROM <VIEW_NAME1> <ALIAS>
, <TABLE_NAME2> SD
WHERE <ALIAS>.<COL3> = SD.<COL4>
AND <ALIAS>.<COL5> = <ALIAS0>.<COL6>
)
;
2 3 4 5 6 7 8 9 AND <ALIAS>.<COL5> = <ALIAS0>.<COL6>
*
ERROR at line 7:
ORA-00900: invalid SQL statement - Above VIEW <VIEW_NAME1> is UNION ALL view of query over dblink as following:
CREATE OR REPLACE VIEW <VIEW_NAME1>
(<COL3>, <COL5>, <COL2>) AS
select
<ALIAS1>.<COL3>,<ALIAS1>.<COL5>,<ALIAS1>.<COL2>
from
<TABLE_NAME3>@<DBLINK> <ALIAS1>
,<TABLE_NAME4>@<DBLINK> <ALIAS2>
where
<ALIAS1>.<COL7> = <ALIAS2>.<COL7>
union all
select
<ALIAS1>.<COL3>,<ALIAS1>.<COL5>,<ALIAS1>.<COL2>
from
<TABLE_NAME3>@<DBLINK> <ALIAS1>
,<TABLE_NAME4>@<DBLINK> <ALIAS2>
where
<ALIAS1>.<COL7> = <ALIAS2>.<COL7>
; - The error stack is following:
ksesecl0 <- msqopnws <- msqbin <- k2copnws <- msqant
<- msq_logical <- msqsel <- msqsub <- msqopnws <- msqbin
<- k2copnws <- msqopnws <- msqsel <- msqsub <- msqtnm
<- msqfrmcl <- msqsel <- msqsub <- msqopnws <- msqopnws
<- msqupd <- msqrxq3 <- msqrxq2 <- msqrxq <- kkqfppPsh
<- kkqfppPsh <- kkqfppDrv1 <- kkqljpFixUpCB <- qksqbApplyToQbc <- apaqbdDescendents
<- apaqbdDescendents <- apaqbdDescendents <- apaqbd <- apadrv <- opitca
<- kksFullTypeCheck <- rpiswu2 <- kksLoadChild <- kxsGetRuntimeLock - Optimizer trace shows that "Invalid predicate" during Old style Join Predicate Pushdown:
OJPPD: Invalid predicate "<ALIAS>"."<COL5>"=:B1
OJPPD: Invalid predicate "<ALIAS>"."<COL5>"=:B1
Changes
Migrate from 11.2 to 19.3.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |