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.