title: How to use an Oracle Associative Array in a SQL query
body: <p>ODP.Net exposes the ability to pass Associative Arrays as params into an Oracle stored procedure from C#.  Its a nice feature unless you are trying to use the data contained within that associative array in a sql query.</p>
  
  <p>The reason for this is that it requires a context switch - SQL statements require SQL types and an associative array passed into PL/SQL like this is actually defined as a PL/SQL type.  I believe any types defined within a PL/SQL package/procedure/function are PL/SQL types while a type created outside these objects is a SQL type (if you can provide more clarity on that, please do but its not the goal of this question).</p>
  
  <p>So, the question is, what are the methods you would use to convert the PL/SQL associative array param into something that within the procedure can be used in a sql statement like this:</p>
  
  <pre><code>OPEN refCursor FOR
  SELECT T.*
  FROM   SOME_TABLE T,
         ( SELECT COLUMN_VALUE V
           FROM   TABLE( associativeArray )
         ) T2
  WHERE  T.NAME = T2.V;
  </code></pre>
  
  <p>For the purposes of this example, the "associativeArray" is a simple table of varchar2(200) indexed by PLS_INTEGER.  In C#, the associativeArry param is populated with a string[].</p>
  
  <p><strong>Feel free to discuss other ways of doing this besides using an associative array but know ahead of time those solutions will not be accepted.  Still, I'm interested in seeing other options.</strong></p>