How to use an Oracle Associative Array in a SQL query

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 FORSELECT T.*FROM   SOME_TABLE T,       ( SELECT COLUMN_VALUE V         FROM   TABLE( databaseArray )       ) T2WHERE  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;/