Re: select for update on 7.3.2

From: Aram Meguerian <aram_at_unisys.com.br>
Date: 1997/03/08
Message-ID: <01bc2bc2$0fa8c5e0$4104dcc8@psaphos>#1/1

James Powrie <jp_at_manip.demon.co.uk> escreveu no artigo <33204503.4409_at_manip.demon.co.uk>...

> Has anyone experienced a problem with 'select for update' queries on
> 7.3.2. We are
> considering upgrading from 7.2.3 to 7.3.2 and our acceptance testing has
> revealed
> a problem which hangs the unix process associated with the session that
> performs
> the select ..... for update query. Although I have experienced the
> problem on two seperate instances, I don't yet have a definitive recipe
> for ORACLE to reproduce the problem.

We have just worked around some problems whith select ... for update cursors. I have advised its use whenever we were certain the selected rows would be updated/deleted at the end of a iteraction.

I have used this with Oracle 7.0 e 7.1 and never got any problem. However, with version 7.3.2 if we declared a cursor using the for update clause and used it inside a loop where commit's and rollback's where issued, every time we rollbacked the transaction we were presented with an 'ORA-01002: fetch out of sequence' error and from that point on every fetch we tried to execute on the for update cursor returned the same exception.

We noticed that it happened only after a rollback was executed and never with a commit. We asked Oracle Support to no avail and ended up trying some variations until a Jr.Prog. began to use savepoints at the begining of the loops and issuing rollback to savepoint instead full rollbacks and have all hers applications running smoothly. We applied this to every other program with this problems and got no more for update errors.

May be you are having the same problem, in which case your Oracle support may not know about it yet as our support didn't know about it at that time.

Hope to have been useful,

                     Aram Meguerian
                     aram_at_unisys.com.br

-------------------------------------------------------------------
   "There's no such a thing as a free lunch in the Universe."
          Lazarus Long Notebook's, Lazarus Long
		from Time enough for love, by Robert A. Heinlein
-------------------------------------------------------------------

I don't work at Unisys, it is just my Internet Provider, so don't blame it for anything I have just said.

Received on Sat Mar 08 1997 - 00:00:00 CST