Handling unique constraint error and trying to auto generate a unique value upto n times using a loop

Question

I'm trying to insert a auto generated value into a table. If there is a unique constraint error, I want to get a new value and insert the latest value into the table. I want to try to generate the unique value for up to 10 times before throwing the unique constraint error.

This is what I have so far.

BEGIN
    autoGenValue := x
FOR i in 1..10 LOOP
    BEGIN
      INSERT INTO mainTbl(A,
                   B, 
                   C)
      VALUES(autoGenValue,
               b,
               c ) 
    EXCEPTION
          WHEN DUP_VAL_ON_INDEX
            THEN
                SELECT lpad(NVL(MAX(SUBSTR(A, -2)), '00')+1,4,'0')
                INTO maxSeq
                FROM RecvTbl

                SELECT car_id||maxSeq
                INTO autoGenValue 
                FROM DUAL;

         WHEN OTHERS THEN
             EXIT;          
     END;
   END LOOP;

    UPDATE RecvTbl
         SET A = autoGenValue 
         WHERE A = x;  
END;
COMMIT;      

x is the initially calculated auto generated value which I try to insert to the mainTbl. If there is a unique constraint error I follow some required logic (simplified here) to get the required unique value. I try this for 10 times (if required). Exit when there is no unique constraint error and update recvTbl also with the value as I need it for future calculation.

With my code I've noticed that the value was being incremented after inserting into the mainTbl even when there was no unique constraint error and the value inserted in recvTbl was an incremented value instead of the value in the mainTbl.

Any ideas what I'm doing wrong?

Answer 1

The issue is that you have no exit on success.....

You exit the loop if there is any ERROR other than DUP_VAL_ON_INDEX but continue the loop if the insert succeeds.

So the next time through the loop you will get a DUP_VAL_ON_INDEX error and increment.

You could use something like the below (untested), to set a success variable and then exit the loop as soon as it is true.

BEGIN
autoGenValue := x;
v_success := false;
FOR i in 1..10 LOOP
    BEGIN
      INSERT INTO mainTbl(A,
                   B, 
                   C)
      VALUES(autoGenValue,
               b,
               c ) ;
      v_success := true;
    EXCEPTION
          WHEN DUP_VAL_ON_INDEX
            THEN
                SELECT lpad(NVL(MAX(SUBSTR(A, -2)), '00')+1,4,'0')
                INTO maxSeq
                FROM RecvTbl

                SELECT car_id||maxSeq
                INTO autoGenValue 
                FROM DUAL;

     WHEN OTHERS THEN
     EXIT;          
     END;
     if v_success = true then
       exit;
     end if;
   END LOOP;

    UPDATE RecvTbl
         SET A = autoGenValue 
         WHERE A = x;  
END;
COMMIT;