"Where x in arr" is invalid syntax! So the subquery method is better because it works ;)
Note you also have to pass the array into a table() operator:
create or replace type vchar_arr is table of varchar2(10);
/
declare
arr vchar_arr := vchar_arr('X', 'Y', 'Z');
begin
for rws in (select * from dual where dummy in arr) loop
dbms_output.put_line(rws.dummy);
end loop;
end;
/
Error report -
ORA-06550: line 4, column 49:
PL/SQL: ORA-00932: inconsistent datatypes: expected CHAR got CHRIS.VCHAR_ARR
declare
arr vchar_arr := vchar_arr('X', 'Y', 'Z');
l varchar2(10);
begin
for rws in (
select * from dual
where dummy in (select column_value from arr)
) loop
dbms_output.put_line(rws.dummy);
end loop;
end;
/
declare
arr vchar_arr := vchar_arr('X', 'Y', 'Z');
l varchar2(10);
begin
for rws in (
select * from dual
where dummy in (select column_value from table(arr))
) loop
dbms_output.put_line(rws.dummy);
end loop;
end;
/
Error report -
ORA-06550: line 7, column 46:
PL/SQL: ORA-00942: table or view does not exist
PL/SQL procedure successfully completed.
X