I am writing a PL/SQL script which fetches records from a cursor, performs
edits, inserts them into another table, and then updates the original table
via a stored procedure. What I would like to do is ROLLBACK all transactions
if I encounter an exception but my ROLLBACK doesn't seem to be doing what
I want. The reference materials I have perused are not clear on this, but
I am guessing that one cannot use SAVEPOINT until a database transaction
is performed, because the ROLLBACK TO doesn't seem to be undoing all changes.
Since all the examples I can find show SAVEPOINT following an INSERT or
UPDATE call, I am wondering if this is why it doesn't work or whether I
have been overlooking something.
Below is a (simplified) sample of the coding in question:
BEGIN
SAVEPOINT begining_of_proc;
FOR v_getrec IN getrec_cur LOOP
BEGIN
v_rec_count := v_rec_count + 1;
/* various data edits */
/* INSERT statement */
/* stored procedure call to update v_getrec record on table */
EXCEPTION
WHEN update_error THEN
DBMS_OUTPUT.put_line('Unable to update table');
ROLLBACK TO begining_of_proc;
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('No records to INSERT were found');
ROLLBACK TO begining_of_proc;
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.put_line('Data value error in record');
ROLLBACK TO begining_of_proc;
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.put_line('Duplicate record encountered');
ROLLBACK TO begining_of_proc;
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error: ' || SQLERRM);
ROLLBACK TO begining_of_proc;
END;
END LOOP;
COMMIT;
DBMS_OUTPUT.put_line(Records processed:' || v_rec_count);
END;
Received on Tue Dec 02 2003 - 13:14:35 CST
