Re: Fetch out of sequence in cursor
- From: "oradbamohan@xxxxxxxxx" <oradbamohan@xxxxxxxxx>
- Date: 30 Aug 2006 08:32:14 -0700
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;
.
- Follow-Ups:
- Re: Fetch out of sequence in cursor
- From: DA Morgan
- Re: Fetch out of sequence in cursor
- References:
- Fetch out of sequence in cursor
- From: PAUL MADDSION
- Fetch out of sequence in cursor
- Prev by Date: Re: How to free unused space in a tablespace?
- Next by Date: Re: Calling an SQL function from within an Oracle trigger
- Previous by thread: Re: Fetch out of sequence in cursor
- Next by thread: Re: Fetch out of sequence in cursor
- Index(es):
Relevant Pages
|
