Live ORACLE

DROP TABLE results CASCADE CONSTRAINTS PURGE;


CREATE TABLE results (
 res_name   VARCHAR(20),
 res_answer VARCHAR2(3));



DROP INDEX res_name_ix;



CREATE UNIQUE INDEX res_name_ix ON results (
     res_name);



INSERT INTO results
VALUES     ('SMYTHE',
           'YES');



INSERT INTO results
VALUES     ('JONES',
           'NO');



DECLARE
 NAME    VARCHAR2(20) := 'SMYTHE';
 answer  VARCHAR2(3) := 'NO';
 suffix  NUMBER := 1;
BEGIN
 FOR i IN 1.. 5 LOOP -- try 5 times
   BEGIN -- sub-block begins
     SAVEPOINT start_transaction; -- mark a savepoint
     /* Remove rows from a table of survey results. */
     DELETE FROM results
     WHERE       res_answer = 'NO';
     /* Add a survey respondent's name and answers. */
     INSERT INTO results
     VALUES     (NAME,
                 answer);
     -- raises DUP_VAL_ON_INDEX if two respondents have the same name
     COMMIT;
     EXIT;
   EXCEPTION
     WHEN dup_val_on_index THEN
       ROLLBACK TO start_transaction; -- undo changes
       suffix := suffix
                   + 1; -- try to fix problem
       NAME := NAME
               ||To_char(suffix);
   END; -- sub-block ends
 END LOOP;
END;
/  

Retrying a Transaction After an Exception