How to use an Oracle Associative Array in a SQL query

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 s开发者_开发知识库tatements 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.

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

You cannot use associative arrays in the SQL scope - they are only usable in the PL/SQL scope.

One method is to map the associative array to a collection (which can be used in the SQL scope if the collection type has been defined in the SQL scope and not the PL/SQL scope).

SQL:

CREATE TYPE VARCHAR2_200_Array_Type AS TABLE OF VARCHAR2(200);
/

PL/SQL

DECLARE
  TYPE associativeArrayType IS TABLE OF VARCHAR2(200) INDEX BY PLS_INTEGER;
  i                PLS_INTEGER;
  associativeArray associativeArrayType;
  array            VARCHAR2_200_Array_Type;
  cur              SYS_REFCURSOR;
BEGIN
  -- Sample data in the (sparse) associative array
  associativeArray(-2) := 'Test 1';
  associativeArray(0)  := 'Test 2';
  associativeArray(7)  := 'Test 3';

  -- Initialise the collection
  array := VARCHAR2_200_Array_Type();

  -- Loop through the associative array
  i := associativeArray.FIRST;
  WHILE i IS NOT NULL LOOP
    array.EXTEND(1);
    array(array.COUNT) := associativeArray(i);
    i := associativeArray.NEXT(i);
  END LOOP;

  -- Use the collection in a query
  OPEN cur FOR
    SELECT *
    FROM   your_table
    WHERE  your_column MEMBER OF array;
END;
/