Resetting an Associative array in PL/SQL?

Question

This is one of those "there's gotta be a better way" questions. Let me set up the problem, then I'll give you my hacked solution, and perhaps you can suggest a better solution. Thanks!

Lets take this little tidbit of PL/SQL

DECLARE
 TYPE foo_record IS RECORD (foo%type, bar%type);
 TYPE foo_records IS TABLE OF foo_record INDEX BY PLS_INTEGER;
 arr_foos foo_records;

 CURSOR monkeys is SELECT primates FROM zoo;
 row_monkey monkeys%rowtype;
BEGIN
 FOR row_monkey IN monkeys loop
  /*
    at this point in each iteration I need to have the associative array 
    arr_foos in its original state. if this were java, I'd declare it 
    right here and its scope would be limited to this iteration. However, 
    this is not java, so the scope of the array is effectively global and 
    I can't have one iteration's data meddle with the next.
  */
  null;
 END LOOP;
END;

Does that make sense? I basically need to reset it to something. If it was a number that starts at zero, I could just say number:=0; at the top of every iteration and be done with it. But this is not a number, it's a type that I can just reset with a clean :=0.

Anyway, onto my hack:

DECLARE
 TYPE foo_record IS RECORD (foo%type, bar%type);
 TYPE foo_records IS TABLE OF foo_record INDEX BY PLS_INTEGER;
 arr_foos foo_records;
 arr_foos_reset foo_records;

 CURSOR monkeys is SELECT primates FROM zoo;
 row_monkey monkeys%rowtype;
BEGIN
 FOR row_monkey IN monkeys loop
  arr_foos := arr_foos_reset;
  null;
 END LOOP;
END;

I figured that if I can manage to preserve a member of the same type in an original state, then I can just set the working variable back to whatever the value is of the original. And, surprisingly enough, it works (I think.) But there's gotta be a better way. Can anyone help?

T'anks!

Answer 1

The easiest way:

arr_foos.Delete();

Other way is to declare the variable inside the FOR loop. This way it will be recreated for each pass.

Like this:

DECLARE
 TYPE foo_record IS RECORD (foo%type, bar%type);
 TYPE foo_records IS TABLE OF foo_record INDEX BY PLS_INTEGER;     

 CURSOR monkeys is SELECT primates FROM zoo;
 row_monkey monkeys%rowtype;
BEGIN
 FOR row_monkey IN monkeys loop
  DECLARE
    arr_foos foo_records;
  BEGIN
    null;
  END;
 END LOOP;
END;

Answer 2

Are you going to read data from zoo table into a collection? Then there's a better way:

DECLARE
  type foos_ts is table of zoo.foo%type index by pls_integer;
  foos foos_t;
BEGIN
  select foo
  bulk collect into foos
  from zoo;
  ...
END;

Bulk collect automatically clears the collection before fetching, and it works faster then reading row-by-row in loop. Unfortunately, it does'n work with records, so you'll need several PL/SQL tables for each of field.

You can find additional information here: Retrieving Query Results into Collections with the BULK COLLECT Clause