How can I use an array variable inside the IN operator for Oracle SQL?

Question

I declare a type TYPE arr_type is TABLE of VARCHAR2(11 BYTE); and then I initialize it: MY_array arr_type := arr_type();.

I insert some varchars into it, and then attempt to use it with an IN operator.

set serveroutput on;
DECLARE
  TYPE arr_type is TABLE of VARCHAR2(11 BYTE);
  my_array arr_type := arr_type();
  my_array_two arr_type := arr_type();
BEGIN 

  SELECT MY_ID BULK COLLECT INTO my_array FROM XYZ_REQUEST;
  SELECT ANOTHER_ID BULK COLLECT INTO my_array_TWO FROM ABC_REQUEST WHERE PARENT_ID IN my_array;

   FOR i IN 1..cm_array.COUNT LOOP
     DBMS_OUTPUT.PUT_LINE(my_array(i));
   END LOOP; 
END; 
/

I get the error: local collection types not allowed in SQL statements on the line containing: SELECT ANOTHER_ID BULK COLLECT INTO my_array_TWO FROM ABC_REQUEST WHERE PARENT_ID IN my_array;, but it doesn't make sense because if I comment out that line, my_array prints fine, which means TYPE arr_type is TABLE of VARCHAR2(11 BYTE);.

I suspect the issue is because I'm trying to use an unbounded array with the IN operator i.e. PARENT_ID IN my_array.

I tried wrapping the variable inside parenthesis, but it doesn't help. How can I use the IN operator with an array?

I understand that in this case I can use subqueries or joins, but I am wondering is it possible to use an array with an IN operator.

Answer 1

WHERE PARENT_ID IN my_array;

This will not work. First, as the error message states, you are not allowed to use local collection types in SQL statements, you need to define them in the database. Second, that syntax does not exist.

So first you define the type:

CREATE TYPE arr_type is TABLE of VARCHAR2(11 BYTE);

And after that, you can use the collection with the TABLE() function as below:

set serveroutput on;
DECLARE
  my_array arr_type := arr_type();
  my_array_two arr_type := arr_type();
BEGIN 

  SELECT MY_ID BULK COLLECT INTO my_array FROM XYZ_REQUEST;
  SELECT ANOTHER_ID BULK COLLECT INTO my_array_TWO FROM ABC_REQUEST
  WHERE PARENT_ID IN (select * from table(my_array)); -- <==========================

   FOR i IN 1..cm_array.COUNT LOOP
     DBMS_OUTPUT.PUT_LINE(my_array(i));
   END LOOP; 
END; 
/