How to use an Oracle Associative Array in a SQL query

Question

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.

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).

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:

OPEN refCursor FOR
SELECT T.*
FROM   SOME_TABLE T,
       ( SELECT COLUMN_VALUE V
         FROM   TABLE( associativeArray )
       ) T2
WHERE  T.NAME = T2.V;

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[].

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.

Answer 1

I would create a database type like this:

create type v2t as table of varchar2(30);
/

And then in the procedure:

FOR i IN 1..associativeArray.COUNT LOOP
    databaseArray.extend(1);
    databaseArray(i) := associativeArray(i);
END LOOP;

OPEN refCursor FOR
SELECT T.*
FROM   SOME_TABLE T,
       ( SELECT COLUMN_VALUE V
         FROM   TABLE( databaseArray )
       ) T2
WHERE  T.NAME = T2.V;

(where databaseArray is declared to be of type v2t.)