Re: Using UPDATE with ROWNUM?

On Jun 27, 9:59 pm, Hasta <hasta..._at_hotmail.com> wrote:
> In article <1182982006.101947.222..._at_a26g2000pre.googlegroups.com>,
> joel-ga..._at_home.com says...
>
>
>
>
>
> > On Jun 27, 6:55 am, Jagjeet Singh <jagjeet.ma..._at_gmail.com> wrote:
> > > On Jun 27, 6:49 pm, Jagjeet Singh <jagjeet.ma..._at_gmail.com> wrote:
>
> > > > > I don't know what this is other than one of the worst examples of PL/SQL
> > > > > I have seen in a very long time. And I teach students new to PL/SQL.
>
> > > > what other way you can suggest ?
>
> > > Simple and Best and Fastest way is to use just single update, No
> > > intermediate commit which may leave
> > > you with inconsistent data. But he mentioned he can not take a lock
> > > more than few seconds.
>
> > Um, you might want to peruse this internet thingee about commit in
> > loop and ora-1555, for example at asktom.oracle.com, especially if
> > there is a performance requirement.
>
> But why would the OP get ora-1555, Joel (and Frank and Daniel) ?
>
> He is not commiting in a cursor loop, afaics
>
> begin
> loop
> update (select seq_no from mytable) set seq_no = tempseq.nextval
> where rownum <= 1000;
> exit when SQL%FOUND = false;
> commit;
> end loop;
> end;
>
> --- Raoul

He's not?
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#i45288

Search for ora-1555 at asktom or read his books. Jagjeet's comment about order by may be a reasonable response to this, most people who are committing in loops and post here about it haven't considered such issues.

jg

--
@home.com is bogus.
http://www.nao.org.uk/intosai/edp/intoit_articles/18p60top62.pdf