what does the stored proc to update look like? specifically does it commit.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Ubiquitous" <weberm_at_polaris.net> wrote in message
news:bqioar$3in$1_at_news.utelfla.com...
> 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 Wed Dec 03 2003 - 03:31:51 CST
