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:

Not quite sure whether we are in sync on definition of transaction.

Simplest definition I use: A transaction has one or more DML (insert, update, delete, merge) statements in which the entire group of statements is terminated with a COMMIT or ROLLBACK. AFAIK, a transaction can not involve DML outside the current session. Also, a COMMIT/ROLLBACK (even an implied one) generally ends a transaction and your can not 'rollback into a previous commit'.

Savepoint - set a mark within current transaction.

Rollback - undo/reverse current transaction to indicated mark or to beginning.

I get the impression you want to go back to a previous point in time. If so, you really want to investigate Oracle's FlashBack Query. Received on Tue Dec 02 2003 - 14:52:35 CST