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

Answer 2

Oracle offers a built-in type (sys.odcinumberlist) that is capable of containing numbers (so that you wouldn't have to create your own); that's what my example does. I'm using Scott's sample schema; the emp table contains some employees who work in certain departments - list of departments will be passed to the procedure.

SQL> create or replace procedure getdata
  2    (ids in sys.odcinumberlist, results out sys_refcursor)
  3  is
  4  begin
  5    open results for
  6      select empno, ename from emp
  7      where deptno in (select * from table(ids));   --> this is what you're looking for
  8  end;
  9  /

Procedure created.

Let's try it:

SQL> set serveroutput on
SQL> declare
  2    l_ids   sys.odcinumberlist;  -- it'll contain list of departments
  3    l_rc    sys_refcursor;       -- refcursor will be returned into this variable
  4    l_empno number;              -- employee number
  5    l_ename varchar2(20);        -- their name
  6  begin
  7    getdata(sys.odcinumberlist(10, 20), l_rc);   -- this is how you call the procedure
  8
  9    loop                                         -- you'll be using the result differently; I'm just
 10      fetch l_rc into l_empno, l_ename;          -- displaying contents
 11      exit when l_rc%notfound;
 12      dbms_output.put_line(l_empno ||' - '|| l_ename);
 13    end loop;
 14  end;
 15  /
7782 - CLARK
7839 - KING
7934 - MILLER
7369 - SMITH
7566 - JONES
7788 - SCOTT
7876 - ADAMS
7902 - FORD

PL/SQL procedure successfully completed.

SQL>

---

You can use your own type, of course; it's just that I didn't feel like creating a package and do everything there because type (the one you posted) can't be created at SQL level:

SQL> create or replace TYPE Ids_t_a is table of Number index by binary_integer;
  2  /

Warning: Type created with compilation errors.

SQL> show err
Errors for TYPE IDS_T_A:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/17     PLS-00355: use of pl/sql table not allowed in this context
SQL>