Merge update insert error. ORA-30926

[DarkWorlds]

The following code errors with:

ORA-30926: unable to get a stable set of rows in the source tables
Cause: A stable set of rows could not be got because of large dml activity or a non-deterministic where clause.

Action: Remove any non-deterministic where clauses and reissue the dml.

What am I missing and how can I fix this. The tables are simple and both tables are the same. One is from jan the other is from mar. Just trying to update the old, and insert the new.

The code is as follows.

MERGE INTO UPD_TEMP_1 b
USING (
SELECT S_NUMBER, CUST_NUMBER, PAY_MODE, ACT_DATE, INACT_DATE
FROM TEMP_2) e
ON (b.S_NUMBER = e.S_NUMBER and b.CUST_NUMBER = e.CUST_NUMBER)
WHEN MATCHED THEN
UPDATE SET b.PAY_MODE = e.PAY_MODE, b.ACT_DATE = e.ACT_DATE, b.INACT_DATE = e.INACT_DATE
WHEN NOT MATCHED THEN
INSERT (b.S_NUMBER, b.CUST_NUMBER, b.PAY_MODE, b.ACT_DATE, b.INACT_DATE)
VALUES (e.S_NUMBER, e.CUS_NUMBER, e.PAY_MODE, e.ACT_DATE, e.INACT_DATE)

 

[DarkWorlds]

Both tables refenced are using the same primary key, which is s_number + cust_number...

Noticed I have in values e.cus_number instead of cust_number. This error is not present in the code and is correct. So for the code written here is should be e.cust_number. Sorry about that. *wishs there was a way to edit posts*

 

[Dagon]

What sort of table is TEMP2 ? Is it a global temporary table or anything like that ? Is the data set in it stable or is it being updated at the time the merge is being run ?

 

[DarkWorlds]

You found my error. I just wraped my head around this project for so long I wasnt fully breaking down the issue. Thanks. Works flawlessly now.