ORA-900 Caused By Update Statement With Unian All View Over Dblink (Doc ID 2697013.1)

Last updated on SEPTEMBER 23, 2020

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.