Re: Fetch out of sequence in cursor

Thanks for the advice.

Yes, I was using a for update in my cursor. I wanted to mark the
successfully processed records so that I could delete them all in one go at
the end of the procedure. I suppose the way round this is to delete them as
I go (once I know processing have been successful) and issue a commit every
x number of records.

Paul

"Andy Hassall" <andy@xxxxxxxxxxx> wrote in message
news:50r5f21jl0sdln9sesov1mk89t7d145c33@xxxxxxxxxx
On Mon, 28 Aug 2006 10:26:47 GMT, "PAUL MADDSION" <60_pm@xxxxxxxxxxxxxxxx>
wrote:

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.

PROCEDURE update_rec
IS
--
CURSOR c_record ...
--
processed_rec c_record%ROWTYPE;
--
BEGIN
FOR r_record IN c_record LOOP
--
BEGIN
...
-- if I get this far I want to commit individual records
COMMIT;
EXCEPTION
ROLLBACK;
handle any exceptions raised in the loop
END LOOP;

The commit in the loop is dodgy practice. But it could be the rollback
that's
finishing you off:

http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html

Or you're using FOR UPDATE in which case it'll fail straight away.

--
Andy Hassall :: andy@xxxxxxxxxxx :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool


.

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: 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)
  • Re: Fetch out of sequence in cursor
    ... I was using a for update in my cursor. ... I wanted to mark the ... successfully processed records so that I could delete them all in one go at ... I'm not sure why you don't want to commit once. ...
    (comp.databases.oracle.misc)