Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Tuan.

Asked: December 01, 2016 - 7:36 am UTC

Last updated: December 01, 2016 - 9:40 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Dear Tom,
Can i ask question.
If i have a array of varchar2 with name is arr_val in oracle. How can i use "where in arr_val" instead of "where in (select column_name from table_name)".And if it can be done. Can y explain which way is better?
Thank you!

Regards,
Tuan Khac

and Chris said...

"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


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library