I discovered what was happening. Here's my original code:
> 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;
After looking at this for a couple days, I realized it was caused by my
EXCEPTION statements being located within the LOOP logic, resulting in only
the INSERTS performed before the exception getting rolled back. If no more
exceptions were encountered, subsequent rows that were inserted were
commited. I solved the problem by replacing the ROLLBACK TO line in the
exceptions within the loop with RAISE NO_DATA_FOUND, removing the OTHERS
exception within the loop, and adding exception handling outside the loop
for NON_DATA_FOUND and OTHERS with the ROLLBACK TO begining_of_proc. Thanks
for all your help! :-)
Received on Thu Dec 11 2003 - 08:51:10 CST
