Fetch out of sequence in cursor

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;


.

Relevant Pages

  • Re: Straight SQL always put perform PL/SQL?
    ... end loop; ... CURSOR test_cur IS ... PL/SQL procedure successfully completed. ...
    (comp.databases.oracle.server)
  • Re: BULK COLLECT - Can it be implemented for a few rows .
    ... So we designed a NEW PROC using BULK COLLECT. ... CURSOR CUR2IS SELECT DISTINCT ... COMMIT; ... FOR I IN CUR1LOOP ...
    (comp.databases.oracle.tools)
  • Re: Performance improvment -loop update.
    ... For c1_rec in c1 Loop ... table Ems_Currentsoexpedite_Tmp is having 8000 records and cursor ... of a "Job Jar" that only seemed to return one job for him to do ...
    (comp.databases.oracle.tools)
  • Re: Fetch out of sequence in cursor
    ... I was using a for update in my cursor. ... successfully processed records so that I could delete them all in one go at ... Towards the end of the loop I update another table and I want to COMMIT ...
    (comp.databases.oracle.misc)
  • Re: Very strange problem.
    ... end loop; ... Because the number of record is very large, so I want to commit every 1000 ... rows, but after commit for the first time, the cursor is not availible any ... >>cursor icur is ...
    (comp.databases.oracle.server)