title: PL/SQL - Loop insert and select
body: <p>I want a reusable script that can be configured by swapping out variable values. I am hoping to be able to loop over a collection of <code>tag_ids</code> and insert a row for each tag. Also I want to have a check either side of the insert for visual verification before committing.</p>
  
  <p>This is what I have tried so far:</p>
  
  <pre><code>set verify off;
  set serveroutput on;
  
  define uname = 'foobar'
  define f_name = 'foo'
  define l_name = 'bar'
  
  declare
   type numListType is table of number;
   numList numListType;
  
  begin
  numList := numListType(432,433,434,435);
  
  for i in numList.FIRST..numList.LAST loop
      dbms_output.put_line('EXPECTING 0');
      select count(*)
      from security_tag_tbl
      where tag_id = numList(i);
  
      dbms_output.put_line('INSERTING ROW');
      insert into security_tag_tbl (id, username, first_name, last_name, tag_id)
      values (security_tag_tbl_seq.nextval, '&amp;uname', '&amp;f_name', '&amp;l_name', numList(i));
  
      dbms_output.put_line('EXPECTING 1');
      select count(*)
      from security_tag_tbl
      where tag_id = numList(i);
  end loop;
  end;
  /
  
  set serveroutput off;
  set verify on;
  </code></pre>
  
  <p>Issues with this sample:</p>
  
  <ul>
  <li>This sample returns errors around the select query. </li>
  <li>When I remove the select statements the sample code returns errors with indexes in 'numList'.</li>
  </ul>
  
  <p>Any suggestions about the correct way to approach this scenario?</p>