Retrying Transactions After Handling Exceptions

To retry a transaction after handling an exception that it raised, use this technique:

Enclose the transaction in a sub-block that has an exception-handling part.
In the sub-block, before the transaction starts, mark a savepoint.
In the exception-handling part of the sub-block, put an exception handler that rolls back to the savepoint and then tries to correct the problem.
Put the sub-block inside a LOOP statement.
In the sub-block, after the COMMIT statement that ends the transaction, put an EXIT statement.

If the transaction succeeds, the COMMIT and EXIT statements are processed.

If the transaction fails, control transfers to the exception-handling part of the sub-block, and after the exception handler runs, the loop repeats.

Example 12-26 Retrying Transaction After Handling Exception

DROP TABLE results;
CREATE TABLE results (
  res_name   VARCHAR(20),
  res_answer VARCHAR2(3)
);
 
CREATE UNIQUE INDEX res_name_ix ON results (res_name);
INSERT INTO results (res_name, res_answer) VALUES ('SMYTHE', 'YES');
INSERT INTO results (res_name, res_answer) VALUES ('JONES', 'NO');
 
DECLARE
  name    VARCHAR2(20) := 'SMYTHE';
  answer  VARCHAR2(3) := 'NO';
  suffix  NUMBER := 1;
BEGIN
  FOR i IN 1..5 LOOP  -- Try transaction at most 5 times.
 
    DBMS_OUTPUT.PUT('Try #' || i);
 
    BEGIN  -- sub-block begins
 
       SAVEPOINT start_transaction;
 
       -- transaction begins
 
       DELETE FROM results WHERE res_answer = 'NO';
 
       INSERT INTO results (res_name, res_answer) VALUES (name, answer);
 
       -- Nonunique name raises DUP_VAL_ON_INDEX.
 
       -- If transaction succeeded:
 
       COMMIT;
       DBMS_OUTPUT.PUT_LINE(' succeeded.');
       EXIT;
 
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE(' failed; trying again.');
        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;
/

Result:

Try #1 failed; trying again.
Try #2 succeeded.

Example 12-26 uses the preceding technique to retry a transaction whose INSERT statement raises the predefined exception DUP_VAL_ON_INDEX if the value of res_name is not unique.