merge causes Unique Constraint violated

Question

MERGE INTO dpr d
USING (select 3333 dpr_qot_id, to_date('1-Jun-07', 'DD-MON-RR') dpr_ts
from dual) s
on (s.dpr_ts = d.dpr_ts and s.dpr_qot_id = d.dpr_qot_id)
when not matched then
insert
(DPR_TS,
DPR_OPEN,
DPR_HIGH,
DPR_LOW,
DPR_CLOSE,
DPR_VOLUME,
DPR_QOT_ID)
values
(to_date('1-Jun-07', 'DD-MON-RR'),
920,
941.5,
915,
933.5,
696218,
3333)
WHEN MATCHED THEN
UPDATE
set DPR_OPEN = 920,
DPR_HIGH = 941.5,
DPR_LOW = 915,
DPR_CLOSE = 933.5,
DPR_VOLUME = 696218;

i have a loop with similar queries (different dates, prices). anyhow sometimes i'm getting error: Unique Constraint (QF.IDX_DPR_ID) violated (DBD ERROR: OCIStmtExecute). the unique constraint is defined on DPR_ID, that is generated by a trigger. any clue how can i fix this? the loop is written in perl. i'd expect, this kind of error would not appear while using merge, don't know what am i doing wrong. i can execute it in pl/sql. the error occures 'only' in the loop. is it any timing problem?

thanks in advance greetings magda

Tony Andrews Tony Andrews · Accepted Answer · 2011-03-31T10:52:17

My first thought would be that maybe the sequence is currently returning values lower than the max value of DPR_ID in the table. Run these queries to see:

1) select last_number from user_sequences 
   where sequence_name = '<name of sequence>';

2) select max(dpr_id) from dpr

If the result from query 1 is lower than the result from query 2 then the sequence is out of step with the table and needs to be amended.

merge causes Unique Constraint violated

1 Answers