SQL & PL/SQL

Hi

I have a cursor that is set up to loop through rows from a select statement (19000 rows in total) and update rows in another table.

The problem i'm facing is that the cursor gets to row 3363 and then stops and PL/SQL procedure successfully completed is displayed.

I have tried to debug this by outputing the %ROWCOUNT of the cursor. When I only run this section of the script all 19000 rows are looped through and displayed on screen, so i dont understand why the cursor does not loop through all rows.

If anybody has any ideas i'd be greatfull


see below for code


create or replace procedure "UPD_CHANGE"
is

userid_v varchar2(4000);
main_id_v varchar2(4000);
test_v varchar2(4000);
cnt_v varchar2(4000);

cursor c1 is
select upper(userid) userid
from change2;

begin


for c1rec in c1 LOOP
cnt_v := c1%ROWCOUNT;
dbms_output.put_line('row in cursor ' ||cnt_v||' - ' ||c1rec.userid||'.');


userid_v := c1rec.userid;


select count(user_id)
into test_v
from line_items
where upper(user_id) = userid_v;


if
test_v = '0'
then
dbms_output.put_line('NOT updated ' ||userid_v||' no entry in line items.');
continue;
end if;


select id into main_id_v
from line_items
where upper(user_id) = userid_v
and date_created =
(select max(date_created)
from line_items
where upper(user_id) = userid_v);



update change
set main_id = main_id_v
where upper(userid) = userid_v;

dbms_output.put_line('updated ' ||userid_v||' with ' ||main_id_v || '.');


END LOOP;


EXCEPTION
WHEN TOO_MANY_ROWS
THEN
NULL;
dbms_output.put_line('failed on ' ||userid_v||'.');



end;