Re: fetching across cursors

I've tried this on my server and its working

here it is....

declare

cursor c1 is select * from emp for update; begin
/* note the r does not have to be declared*/ for r in c1 loop

delete from emp where current of c1;

end loop;

commit;
end;
/

I think this will work , Note that the commit is outside the loop because

if given inside it might violate some locking mechanism and you might get errors like
ora-1002 fetch out of sequence

Doug Cowles wrote in message <377D856F.A47A4CB4_at_bigfoot.com>...
>I know we've been through this topic recently but I think this is an
>easy one.
>Following code - just an opern cursor deleting it's own rows and
>comitting,
>works on Oracle 8.0.5 and gives a Fetch Out of Sequence on 7.3.3.
>Does that make sense?
>Does Oracle 8 allow this type of thing? And, is it against ANSI
>standard?
>
>create table xyz (a NUMBER, b NUMBER)
>alter table xyz add constraint tyz primary key (a);
>(insert a handful of values)
>begin
>declare
>cursor c0 as select * from xyz
>begin
>for v in c0
> delete from xyz where a = v.a;
> commit;
>end loop;
>end;
>
>Fetch out of sequence on 7.3.3 - Procedure complete on Oracle 8.0.5
>(Things may be syntactically off a little bit, but you get the general
>idea)
>
Received on Tue Jul 13 1999 - 03:37:41 CDT