Total Pageviews

2014/07/08

ORA-30926: unable to get a stable set of rows in the source tables when Merging tables

Problem
When I executed the merge SQL statement in my web application
MERGE INTO FMS434FB T1 USING
  (SELECT ACCYR AS ACCYR,
          MONTH AS MONTH,
          ASP AS ASP,
          AMT7,
          AMT9,
          AMT7+AMT9 AS ACC_AMT9
   FROM FMS434FB
   WHERE ACCYR||MONTH = :LAST_YYY_MM) T2 ON(T1.ACCYR||T1.MONTH = :YYY_MM
   AND T1.ASP=T2.ASP) WHEN MATCHED THEN
UPDATE
SET T1.AMT7 = T2.ACC_AMT9

It throw this exception
1:  Caused by: java.sql.SQLException: ORA-30926: unable to get a stable set of rows in the source tables when Merging tables  
2:       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]  
3:       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]  
4:       at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]  
5:       at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]  
6:       at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]  
7:       at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]  


Root Cause
ORA-30926 results from the using statement has duplicate data.
Therefore, as I execute this part of SQL statement
SELECT ACCYR AS ACCYR,
       MONTH AS MONTH,
                ASP AS ASP,
                AMT7,
                AMT9,
                AMT7+AMT9 AS ACC_AMT9
FROM FMS434FB
WHERE ACCYR||MONTH = :LAST_YYY_MM
It really has duplicated data !



No comments: