title: sqlplus - using a bind variable in "IN" clause
body: <p>I am setting a bind variable in a PL/SQL block, and I'm trying to use it in another query's IN expression. Something like this:</p>
  
  <pre><code>variable x varchar2(255)
  
  declare
      x varchar2(100);
  begin
      for r in (select id from other_table where abc in ('&amp;val1','&amp;val2','&amp;val3') ) loop
      x := x||''''||r.id||''',';
      end loop;
      --get rid of the trailing ','
      x:= substr(x,1,length(x)-1);
  
      select x into :bind_var from dual;
  end;
  /
  
  print :bind_var;
  
  select *
  from some_table
  where id in (:bind_var);
  </code></pre>
  
  <p>And I get an error (ORA-01722: Invalid number) on the query that tries to use the bind variable in the "IN" list.</p>
  
  <p>The print statement yiels <code>'123','345'</code> which is what I expect.</p>
  
  <p>Is it possible to use the bind variable like this or should I try a different approach?</p>
  
  <p>(using Oracle 10g)</p>
  
  <hr>
  
  <p>Clarification:</p>
  
  <p>This is for a reconcilliation sort of thing. I want to run</p>
  
  <pre><code>select *
  from some_table
  where id in (select id from other_table where abc in ('&amp;val1','&amp;val2','&amp;val3'))
  </code></pre>
  
  <p>before the main part of the script (not pictured here) deletes a whole bunch of records. I want to run it again afterwards to verify that records in <code>some_table</code> have NOT been deleted. However, the data in <code>other_table</code> DOES get deleted by this process so I can't just refer to the data in <code>other_table</code> because there's nothing there. I need a way to preserve the <code>other_table.id</code> values so that I can verify the parent records afterwards.</p>