SQL & PL/SQL

hi there, i'm having troubles understanding why the following doesn't work. i am attempting to bulk collect some values and based on some processing decide to update the target table for that index.

the forall throws a "ORA-22160: element at index [212] does not exist" error.

...

  -- the transactions to process
  type t_transaction_id is table of transactions.transaction_id%type index by pls_integer;
  v_transaction_ids t_transaction_id;

  -- the rent values of the corresponding transaction ids
  type t_rent is table of transactions.rent%type index by pls_integer;
  v_rents t_rent;
  
  -- sparse index of the disposition sids to update
  type t_sparse_index is table of binary_integer;
  v_sparse_index t_sparse_index := t_sparse_index();

  v_records_to_process_count number := 0;

  begin
    open cur1;
    loop
      fetch cur1 bulk collect into v_transaction_ids limit 1000;
      exit when v_records_to_process_count = cur1%rowcount;
      v_records_to_process_count := cur1%rowcount;
      
      for i in 1..v_transaction_ids.count loop
        v_rents(i) := calculate_rent(v_transaction_ids(i));
        
        if v_rents(i) > 0 THEN
          -- add index to set that will be bulk updated
          v_sparse_index.extend;
          v_sparse_index(v_sparse_index.last) := i;
          end if;
      end loop;
      
      -- bulk update using the sparse index
      forall j in values of v_sparse_index
        update transactions
        set rent = v_rents(j)
        where transaction_id = v_transaction_ids(j);            
    end loop;
    close cur1;

...

can someone additionally explaind when nested table constructors are required? i don't understand why the v_spars_index in my example requires one but i get a compile error if i try to do the same for v_rents. i am assuming that the fetch with cursor into v_transaction_ids causes initialization by default?

any insight most appreciated.

Edited by: user521233 on May 30, 2011 3:53 PM