title: nested table element assignment in pl/sql
body: <p>My requirement in pl/sql nested table is the following:</p>
  
  <p>I have a nested table collection type declared and I populate the elements based on a lookup from a table.
  In cases where the lookup yields more than one row(more than one value for code), then add all those multiple values in the nested table and proceed. Here is where i am stuck.
  I am not able to increment that parent counter "indx" inside the exception to process those multiple rows. Since i am not, it only stores the latest data in the nested table and not all of them. </p>
  
  <pre><code>declare
  TYPE final_coll_typ IS TABLE OF varchar2(100);
  
  l_final_coll final_coll_typ;
  
  MULTI_FETCH EXCEPTION;
  PRAGMA EXCEPTION_INIT(MULTI_FETCH, -1422); -- this is an ora error for exact fetch returns more than the required number of rows
  
  begin
  
  for indx in 1..&lt;count&gt; loop
  
      &lt;some processing logic here&gt;
      select code into l_final_coll(indx) from lookup_tbl where &lt;some filter&gt;;
      exception
      when MULTI_FETCH then
               for p in (select code from lookup_tbl where &lt;some filter&gt;)
               loop
                              l_final_coll(indx) := p.code;
                              dbms_output.put_line(l_final_coll(indx));
                          end loop;
                          continue; -- this is for further processing after the loop
  
  end loop;
  end;
  </code></pre>
  
  <p>Lets say, the first iteration of the counter indx produced only one row data for code. That gets stored in l_final_coll(indx).
  Lets say the next iteration of indx i the main for loop produces 2 rows of values for code. My thought was to catch the exception (ORA-01422) and keep adding these 2 code values in the existing nested table.</p>
  
  <p>So, in effect, my nested table should now have 3 values of code in its element. But, currently, I can only get it to populate 2 of them (the single value from first itreration and the latest value from the next)</p>
  
  <p>Any pointers would be appreciated on how I can accomplish this.</p>
  
  <p>PS: Tried manipulating the counter variables indx and p. But, obviously pl/sql does not allow it for a "for loop".</p>