---------- Original Message -----------
From: Helen Borrie <helebor@...>

> At 06:27 20/08/2008, you wrote:
> >The question is: if you have a result set that you're in the middle of iterating over, and you
> >rollback to a savepoint (created after you started the result set), can you continue to iterate
> >over the result set? It looks like that works fine with rollback-retain and commit-retain, but I
> >was curious about savepoints. Thanks,
>
> What exactly are you talking about here? What do you mean by "result set"? What are you doing

to "iterate over it"?

>
> ./heLen

I don't typically deal with cursors explicitly, but that's probably what this boils down to. If a
client application connects to the database, starts a transaction, issues a SELECT statement and
starts to fetch results from it ("iterating" -- pulling more rows until there are none left), sets
a savepoint so it can safely do (and undo) some work based on the data it's pulling from that
initial SELECT statement, and at some point along the way a rollback-to-savepoint is issued, will
the client still be able to continue pulling rows from that initial SELECT, or will the act of
issuing a rollback-to-savepoint cause it (the cursor?) to no longer be available?

This came up after a discussion of the effects of commit-retaining and rollback-retaining on the
same scenario (can I commit work as I'm performing the tasks that I am slowly pulling from an
initial SELECT, can I rollback but continue to pull more rows from that same statement, etc.) I
believe the rule was that both "commit" and "rollback" end my access to any further rows from that
select/cursor/resultset, while "commit retain" and "rollback retain" do not; so does "rollback to
<<savepoint>>" also preserve my access to further rows from that SELECT? I'm trying to avoid
cacheing the results of that initial SELECT, or use two transactions together.

Does that make more sense?

-Philip