Re: Fetch out of sequence in cursor

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 30 Aug 2006 11:39:55 -0700
Message-ID: <1156963196.221532@bubbleator.drizzle.com>

oradbamohan_at_gmail.com wrote:

> 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;
If I may:

PROCEDURE update_rec IS

TYPE myarray IS TABLE OF <table_name>%ROWTYPE; l_data myarray;

CURSOR c_record IS
SELECT *
FROM <table_name>
WHERE <some_condition>;

BEGIN
   OPEN c_record;
   LOOP
     FETCH c_record BULK COLLECT INTO l_data LIMIT 500;

     FORALL i IN 1..l_data.COUNT
     UPDATE STATEMENT ...;

     EXIT WHEN r%NOTFOUND;

   END LOOP;
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
     ROLLBACK;
END update_rec;
/

I like to discourage legacy cursor loop single-row processing.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Aug 30 2006 - 13:39:55 CDT