Usenet.com

www.Usenet.com

Group Index

Comp Thread Archive from Usenet.com

<-- __Chronological__ --> <-- __Thread__ -->

SAVEPOINT... ROLLBACK question



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;




<-- __Chronological__ --> <-- __Thread__ -->


Usenet.com



Please check out one of the premium Usenet Newsgroup Service Providers below for access to Usenet.