How can I use an array variable inside the IN operator for Oracle SQL?
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)); -- &lt;==========================

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