Thursday, May 21, 2009

How to with cursor

DECLARE
rowiden varchar2(15);
inc number;
CURSOR c1
IS
select lov0_.ROW_ID as ROW1_17_
from sebl_dev.S_LST_OF_VAL lov0_
where (lov0_.TYPE in ('UT_MARKET_TYPE'))
and (lov0_.ACTIVE_FLG = 'Y')
order by lov0_.NAME, lov0_.ORDER_BY asc;

BEGIN
rowiden := null;
inc := 0;

open c1;
loop
fetch c1 into rowiden;
exit when c1%notfound;
inc := inc + 1;
-- Danger.. make sure the proper id is given.
update sebl_dev.S_LST_OF_VAL set order_by = inc where row_id = rowiden ;
end loop;

close c1;
COMMIT;
END;