Retrying a Failed Transaction

After an exception is raised, rather than abandon your transaction, you might want to retry it. The technique is:

1. Encase the transaction in a sub-block.

2. Place the sub-block inside a loop that repeats the transaction.

3. Before starting the transaction, mark a savepoint. If the transaction succeeds, commit, then exit from the loop. If the transaction fails, control transfers to the exception handler, where you roll back to the savepoint undoing any changes, then try to fix the problem.

In the following example, the INSERT statement might raise an exception because of a duplicate value in a unique column. In that case, we change the value that needs to be unique and continue with the next loop iteration. If the INSERT succeeds, we exit from the loop immediately. With this technique, you should use a FOR or WHILE loop to limit the number of attempts.

SET SERVEROUTPUT ON;

DECLARE

name VARCHAR2(20);

ans1 VARCHAR2(3);

ans2 VARCHAR2(3);

ans3 VARCHAR2(3);

suffix NUMBER := 1;

BEGIN

FOR i IN 1..10 LOOP -- try 10 times

BEGIN -- sub-block begins

SAVEPOINT start_transaction; -- mark a savepoint

/* Remove rows from a table of survey results. */

DELETE FROM results WHERE answer1 = 'NO';

/* Add a survey respondent's name and answers. */

INSERT INTO results VALUES (name, ans1, ans2, ans3);

-- 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;

/

Using Locator Variables to Identify Exception Locations

Using one exception handler for a sequence of statements, such as INSERT, DELETE, or UPDATE statements, can mask the statement that caused an error. If you need to know which statement failed, you can use a locator variable:

DECLARE

stmt INTEGER;

name VARCHAR2(100);

BEGIN

stmt := 1; -- designates 1st SELECT statement

SELECT table_name INTO name FROM user_tables WHERE table_name LIKE 'ABC%';

stmt := 2; -- designates 2nd SELECT statement

SELECT table_name INTO name FROM user_tables WHERE table_name LIKE 'XYZ%';

EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line('Table name not found in query ' || stmt);

END;

/

Handling Exceptions Raised in Declarations

SET SERVEROUTPUT ON;

DECLARE

BEGIN

DECLARE

credit_limit CONSTANT NUMBER(3) := 5000; -- raises an exception

BEGIN

NULL;

EXCEPTION

WHEN OTHERS THEN

-- Cannot catch the exception. This handler is never called.

dbms_output.put_line('Can''t handle an exception in a declaration.');

END;

EXCEPTION

WHEN OTHERS THEN

-- Cannot catch the exception. This handler is never called.

dbms_output.put_line('Dont worry I am here.');

END;