Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Using UPDATE with ROWNUM?
In article <1182982006.101947.222330_at_a26g2000pre.googlegroups.com>,
joel-garry_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;