Oracle Example-1

A procedure to insert and delete data from a table. However if we had an error while inserting or deleting, we can rollback our process.

Retrying a Transaction After an Exception

CREATE TABLE results ( res_name VARCHAR(20), res_answer VARCHAR2(3) );
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;
/

Advertisement