How to use associative array in "where in" clause?

Question

To pass a list/array of ids from C# into Oracle PL/SQL procedure, you have to define an associative array type e.g. a table with integer indexing:

TYPE Ids_t_a is table of Number index by binary_integer;

I want to write a stored proc which wraps a query like the following:

SELECT Id, Name from Person where Id in Ids;

I know basic SQL but am not a PL/SQL expert at all and table-valued types are beyond me. I tried:

PROCEDURE GetData(Ids in Ids_t_a, results out sys_refcursor) IS
BEGIN
Open Results for
SELECT Id, Name from Person p where p.Id in Ids;
END;

But this gives an error "expression is of wrong type" and that seems to be because you cannot use an associative array this way.

What additional steps do I need to run my desired query against my input arguments?

Answer 1

In Oracle, there are two scopes where statements can be evaluated:

1. The SQL scope where the Oracle engine will parse SQL statements; and
2. The PL/SQL scope where the Oracle engine will parse procedural language statements (PL/SQL).

An associative array is a PL/SQL data type and can only be used in the PL/SQL scope. It CANNOT be used in SQL statements so it is impossible to use an associative array directly as you are attempting (and, for some unknown reason, C# does not support passing non-associative arrays).

---

What additional steps do I need to run my desired query against my input arguments?

You need to convert the associative array collection type that you pass in from C# to a non-associative array collection type (either a nested-table collection type or a VARRAY collection type that you have defined in the SQL scope) in the PL/SQL scope and then use the non-associative array in the SQL scope.

You could use a built-in collection type like SYS.ODCINUMBERTYPE or you can define your own collection in the SQL scope:

CREATE TYPE number_list IS TABLE OF NUMBER;

Then you can create your package and procedure and a helper function to perform the conversion:

CREATE PACKAGE your_package IS
  TYPE Ids_t_a is table of Number index by binary_integer;

  FUNCTION map_ids(
    Ids     in  Ids_t_a
  ) RETURN number_list PIPELINED;

  PROCEDURE GetData(
    Ids     in  Ids_t_a,
    results out sys_refcursor
  );
END;
/

Then the package body:

CREATE PACKAGE BODY your_package IS
  FUNCTION map_ids(
    Ids     in  Ids_t_a
  ) RETURN number_list PIPELINED
  IS
    v_idx BINARY_INTEGER;
  BEGIN
    IF ids IS NULL THEN
      RETURN;
    END IF;
    v_idx := ids.FIRST;
    WHILE v_idx IS NOT NULL LOOP
      PIPE ROW(ids(v_idx));
      v_idx := ids.NEXT(v_idx);
    END LOOP;
  END;

  PROCEDURE GetData(
    Ids     in  Ids_t_a,
    results out sys_refcursor
  )
  IS
  BEGIN
    Open Results for
      SELECT Id, Name
      FROM   Person
      WHERE  Id MEMBER OF map_ids(ids);
  END;
END;
/

Note: if you use a VARRAY collection data type, such as the built-in SYS.ODCINUMBERLIST type, then you cannot use the MEMBER OF operator as that only supports nested-table collection types. Instead you would have to use a sub-query (or a join) and a table collection expression. For example: Open Results for SELECT p.Id, p.Name FROM Person p INNER JOIN TABLE(map_ids(ids)) i ON p.id = i.COLUMN_VALUE;

If you have the sample data:

CREATE TABLE person (id, name) AS
SELECT 1, 'Alice' FROM DUAL UNION ALL
SELECT 2, 'Beryl' FROM DUAL UNION ALL
SELECT 3, 'Carol' FROM DUAL UNION ALL
SELECT 4, 'Debra' FROM DUAL;

Then you can call your procedure (either from C# or from a PL/SQL block) and pass an associative array and retrieve the cursor:

DECLARE
  v_cur  SYS_REFCURSOR;
  v_ids  YOUR_PACKAGE.IDS_T_A;
  v_id   PERSON.ID%TYPE;
  v_name PERSON.NAME%TYPE;
BEGIN
  v_ids(1) := 3;  -- Note: These are deliberately not sequential index values.
  v_ids(3) := 1;  --       Indexes generated by C# probably would be, but it 
  v_ids(42) := 7; --       is not guaranteed to always be true.
  YOUR_PACKAGE.GetData(v_ids, v_cur);
  LOOP
    FETCH v_cur INTO v_id, v_name;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_id || ': ' || v_name);
  END LOOP;
END;
/

Which outputs:

1: Alice
3: Carol

fiddle