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.
Best Answer
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:
And after that, you can use the collection with the
TABLE()
function as below: