title: comma separated values in oracle function body
body: <p>I've got following oracle function but it does not work and errors out.  I used <a href="http://asktom.oracle.com/pls/apex/f?p=100:11:0%3a%3a%3a%3aP11_QUESTION_ID:210612357425" rel="nofollow noreferrer">Ask Tom's</a> way to convert comma separated values to be used in <code>select * from table1 where col1 in &lt;&gt;</code></p>
  
  <p>declared in package header:</p>
  
  <pre><code>TYPE myTableType IS table of varchar2 (255);
  </code></pre>
  
  <p>Part of package body:</p>
  
  <pre><code>l_string        long default iv_value_with_comma_separated|| ',';
  l_data          myTableType := myTableType();
  n               NUMBER;
  
  begin
    begin
  LOOP
      EXIT when l_string is null;
      n := instr( l_string, ',' );
       l_data.extend;
       l_data(l_data.count) := ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
       l_string := substr( l_string, n+1 );
  END LOOP;
  end;
  
  OPEN my_cursor FOR
    select * from table_a where column_a in (select * from table (l_data));
  CLOSE my_cursor
  END;
  </code></pre>
  
  <p>above fails but it works fine when I remove </p>
  
  <pre><code>select * from table (l_data)
  </code></pre>
  
  <p>Can someone please tell me what I might be doing wrong here??</p>