4 Replies Latest reply: Mar 31, 2011 5:29 AM by 785962 RSS

    merge causes Unique Constraint (QF.IDX_DPR_ID) violated

    785962
      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).
      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