title: Resetting an Associative array in PL/SQL?
body: <p>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!</p>
  
  <p>Lets take this little tidbit of PL/SQL</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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.</p>
  
  <p>Anyway, onto my hack:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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?</p>
  
  <p>T'anks!</p>