Re: Fetch out of sequence in cursor

Hi,

declare
cursor c1 is select * from emp1;
c_rec c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into c_rec;
exit when c1%notfound;
-- here you use the your 'update' and 'commit' statements -- it wont come from the endof file encounter. dbms_output.put_line('the emp rec..'||c_rec.ename); end loop;
close c1;
end;

PAUL MADDSION wrote:
> Hi,
>
> I have a cursor which loops through a table and does some processing.
> Towards the end of the loop I update another table and I want to COMMIT each
> record in turn. I'm getting a fetch out of sequence message when i try to
> run this and believe it is because I'm trying to commit within the cursor
> loop. Any suggestions on how I can get round this would be greatly
> appreciated. An outline of what I'm trying to do is shown below.
>
> Thanks in advance.
>
> PROCEDURE update_rec
> IS
> --
> CURSOR c_record
> IS
> SELECT
> FROM
> WHERE;
> --
> processed_rec c_record%ROWTYPE;
> --
> BEGIN
> FOR r_record IN c_record LOOP
> --
> BEGIN
> --
> process record
> --
> write to another table
> --
> -- if I get this far I want to commit individual records
> COMMIT;
> EXCEPTION
> ROLLBACK;
> handle any exceptions raised in the loop
> END LOOP;
> --
> EXCEPTION
> WHEN OTHERS THEN
> ROLLBACK;
> handle any exceptions outside the loop
> END;
Received on Wed Aug 30 2006 - 10:32:14 CDT