Re: SAVEPOINT... ROLLBACK question

Ubiquitous wrote:

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

You don't provide enough information but ...

create table test (
testcol varchar2(10));

SQL> savepoint a;

Savepoint created.

SQL> insert into test values ('A');

1 row created.

SQL> savepoint b;

Savepoint created.

SQL> insert into test values ('B');

1 row created.

SQL> rollback to a;

Rollback complete.

SQL> select count(*) from test;

   COUNT(*)

          0

What do yo think it should be doing?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)