Unique constraint violated message on inserting code below

Question

I am writing this code which throws an error in primary key:

DECLARE CURSOR A1 AS
    SELECT 
        TRANS_DET_ID,
        (SELECT MAX (NVL(TRANS_DET_DET_ID, 0) + 1)
         FROM PROD_OPERATIONS_RATE) DET_ID,
        OPER_CODE, ART_CODE, RATE, FROM_DATE, CLOSE_IND 
    FROM 
        PROD_OPERATIONS_RATE
    WHERE 
        TRANS_DET_ID = 1
        AND OPER_CODE = 1
        AND RATE = 2.3005;

a1_var A1%ROWTYPE;
 BEGIN
    
    OPEN A1;
    LOOP
      FETCH A1
        INTO a1_var;
      EXIT WHEN A1%NOTFOUND;
    
      INSERT INTO PROD_OPERATIONS_RATE (
        TRANS_DET_ID,TRANS_DET_DET_ID,OPER_CODE,ART_CODE,RATE,FROM_DATE,CLOSE_IND)
        VALUES (1,a1_var.DET_ID,1,a1_var.ART_CODE,2.50,DATE '2022-05-01','N');
    END LOOP;
    CLOSE A1;
    COMMIT;
 END;

I want to insert data same table throw conditions met, it throws an error of unique constraint on column TRANS_DET_DET_ID which is the primary key. What am I doing wrong? Please can anyone help me with this? Regards

Answer 1

Unique (primary) key value which is calculated as MAX + 1 is almost always wrong. Switch to a sequence.

Find MAX trans_det_det_id value:

SELECT MAX (trans_det_det_id) max_id FROM PROD_OPERATIONS_RATE;

Create sequence as max_id + 1 (I put a dummy value of 1000; you'd use what query actually returns):

CREATE SEQUENCE seq START WITH 1000;    

Now, use the sequence in your PL/SQL script:

DECLARE
   CURSOR A1 IS
      SELECT TRANS_DET_ID,
             --(SELECT MAX (NVL (TRANS_DET_DET_ID, 0) + 1)
             --   FROM PROD_OPERATIONS_RATE) DET_ID,
             OPER_CODE,
             ART_CODE,
             RATE,
             FROM_DATE,
             CLOSE_IND
        FROM PROD_OPERATIONS_RATE
       WHERE     TRANS_DET_ID = 1
             AND OPER_CODE = 1
             AND RATE = 2.3005;

   a1_var  A1%ROWTYPE;
BEGIN
   OPEN A1;

   LOOP
      FETCH A1 INTO a1_var;

      EXIT WHEN A1%NOTFOUND;

      INSERT INTO PROD_OPERATIONS_RATE (TRANS_DET_ID,
                                        TRANS_DET_DET_ID,
                                        OPER_CODE,
                                        ART_CODE,
                                        RATE,
                                        FROM_DATE,
                                        CLOSE_IND)
           VALUES (1,
                   seq.NEXTVAL,     -- a1_var.DET_ID,
                   1,
                   a1_var.ART_CODE,
                   2.50,
                   DATE '2022-05-01',
                   'N');
   END LOOP;

   CLOSE A1;

   COMMIT;
END;

---

By the way, if there's no particular reason for doing it slowly in a loop, use an ordinary INSERT INTO statement (SQL, not PL/SQL), it'll be much faster:

INSERT INTO prod_operations_rate (trans_det_id,
                                  trans_det_det_id,
                                  oper_code,
                                  art_code,
                                  rate,
                                  from_date,
                                  close_ind)
   SELECT trans_det_id,
          seq.NEXTVAL,
          oper_code,
          art_code,
          rate,
          from_date,
          close_ind
     FROM prod_operations_rate
    WHERE     trans_det_id = 1
          AND oper_code = 1
          AND rate = 2.3005;