[Appolgies if this gets double posted, I got an "overquote" bounce. ]
I haven't tried a "where current of" or the "order by" workarounds, but I
usually just push the data into some sort of PL/SQL collection. It's got
the same transactional semantics with no (real) risk of 1555s with only a
minor code tweak. Even if there wasn't a commit in the loop, this might
be a good technique to prevent the RBSs from getting enormous if the inner
part of the loop took an *extremely* long time.
If you do a bulk collect, you get the added benefit of doing a single
round trip, rather than a row by row fetch. (I think 9i or 10g does a
transparent optimization here, turning your row by row fetch into a bulk
under the covers)
> On Tue, 30 Nov 2004, Jared Still wrote:
> > Hi all,
> > Have any of you considered using a 'where current of' update
> > cursor to work around this?
> > I've used it in the past to avoid both ora-1555
and 'fetch across commit'.