plsql - Dealing with PL/SQL Collections -

i have following declaration collection

type t_table1 table of table_1%rowtype index binary_integer; tbl1_u             t_table1; tbl1_i             t_table1; 

this table keep growing , @ end, used in forall loop insert or update on table_1.

now there might cases, want delete element. planning create procedure, take key (unique) , matched element if key found

pseduo code

for in tbl1_u.fist..tbl1_u.last  loop    if tbl1_u(i).key = key      tbl1.delete(i);    end if;  end loop; 

my question is,

1. once delete particular element, collection adjust automatically i.e., index replaced next element or particular index remain null/invalid , possibly give me exception if use in forall insert/update?

2. i don't think can pass table_1%rowtype object procedure, have create record type ?

3. any other tip regarding managing collection bull delete/update/insert appreciate. remeber, dealing 2 tables, if inserting/updating in table_1 means deleting table_2 , vice-versa.

given table_1.key unique might consider using index associative arrays. way can delete collections using key value, according pseudocode available when doing deletions. save having iterate through table find key want, key index - "deletion" pseudo-code become:

tbl1_u.delete(key); 

to answer questions:

1. since you're using associative arrays, when element deleted there no "empty" space in collection. indexes elements, however, don't change. therefore need use collection.prior , collection.next methods loop through collection. again, if use key value index may not need loop through collections @ all.

2. you can pass table_1%rowtype parameter pl/sql procedure or function.

3. you might want consider using merge statement handle doing inserts , updates in 1 step. might allow maintain single collection. might worth looking in to.

share , enjoy.