ORA-06532: Subscript outside of limit

Question

Actually I'm trying to update 1000's of records and I'm doing commit for every 5000 records. but i get the above error due to limit that i have on varray . Please provide alternative for array data type for even storing 100k key values as array! code sample below.

   DECLARE
v_initial number(6):=0;
v_final number(6):=5000;
 -- TOtal number of records V_COUNT 
type lnum IS VARRAY(1000) OF INTEGER; -- change total number of records
iid lnum;
v_maxnum number(8):=0;

BEGIN

iid:=lnum(); -- here 1000 values will be given 

v_maxnum := iid.count;
FOR i in v_initial..v_maxnum LOOP

    UPDATE table SET status='E' WHERE pkey=iid(i);

    IF i=v_final THEN
        COMMIT;
        v_initial:=v_final+1;
        v_final:=v_final+5000;
END IF;
IF i=v_maxnum THEN
         commit;
END IF;

    EXIT WHEN i= v_maxnum;


END LOOP;


END;
/

Answer 1

Initialize v_initial to 1 instead of 0. Varray indexes start at one.

Also, here's another technique for committing every X records you may want to consider for simplicity:

commit_count_const CONSTANT number := 5000; -- commit every 5000
v_loop_counter := 0     
...
FOR i in...
  UPDATE...

  v_loop_counter := v_loop_counter + 1;     -- Increment counter

  -- If the remainder of the counter divided by commit_count_const is 0,
  --  then we hit a multiple of the commit_count so commit.
  IF MOD(v_loop_counter, commit_count_const) = 0 THEN
    COMMIT;
  end if;
...
END LOOP;

COMMIT;  -- commit to catch the rows updated since the last commit.
...

Oh and don't forget to add error handling, what if the UPDATE or COMMIT fails?