oradbamohan@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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org