Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE animals (animal_name) AS
SELECT 'aardvark' FROM DUAL UNION ALL
SELECT 'bat' FROM DUAL UNION ALL
SELECT 'cat' FROM DUAL UNION ALL
SELECT 'dog' FROM DUAL UNION ALL
SELECT 'turtle' FROM DUAL UNION ALL
SELECT 'monkey' FROM DUAL;
6 rows affected

CREATE TABLE pets (pet_name, pet_type) AS
SELECT 'alice', 'aardvark' FROM DUAL UNION ALL
SELECT 'betty', 'bat' FROM DUAL UNION ALL
SELECT 'carol', 'monkey' FROM DUAL;
3 rows affected

declare
  type t_aa is table of varchar2(12);
  v_aa t_aa := t_aa('bat', 'cat', 'dog', 'turtle', 'monkey');
begin
  for r_loop in (select animal_name
                   from animals
                  where animal_name in v_aa)
  loop   
    dbms_output.put_line(r_loop.animal_name);
  end loop;

  for r_loop in (select pet_name
                   from pets
                  where pet_type in v_aa)
  loop   
    dbms_output.put_line(r_loop.pet_name);
  end loop;
end;
/
ORA-06550: line 7, column 40:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 14, column 37:
PLS-00642: local collection types not allowed in SQL statements

CREATE TYPE string_list IS TABLE OF VARCHAR2(12);
declare
  v_aa string_list := string_list('bat', 'cat', 'dog', 'turtle', 'monkey');
begin
  for r_loop in (
    select animal_name
    from   animals
    where  animal_name MEMBER OF v_aa
  )
  loop   
    dbms_output.put_line(r_loop.animal_name);
  end loop;

  for r_loop in (
    select pet_name
    from   pets
    where  pet_type IN (SELECT COLUMN_VALUE FROM TABLE(v_aa))
  )
  loop   
    dbms_output.put_line(r_loop.pet_name);
  end loop;
end;
/
1 rows affected

dbms_output:
bat
cat
dog
turtle
monkey
betty
carol