Oracle how to use array in where clause?

Question

I want store the value in array, and then use it in where clause.

Like this:

SELECT ... ... 
  FROM myTable 
 WHERE (myTable.id, myTable.type) 
    IN (SELECT * 
          FROM table(array_collection)
       )

array_collection value like this:

  (('1','N'),
   ('2','N'),
   ('3','Y'))

And there have any way not create schema level table type to do that?

Answer 1

I want store the value in array.

This is how you store value:

CREATE OR REPLACE TYPE array_item IS object( id NUMBER, TYPE varchar2(3));

CREATE OR REPLACE TYPE array_collection IS VARRAY(10) OF array_item;


declare
--Initialization 
var_array array_collection:=array_collection();
begin
  ---Storing 3 elements
 var_array.extend(3);
 var_array(1):= array_item(1,'N');
 var_array(2):= array_item(2,'N');
 var_array(3):= array_item(3,'Y');

 for i in 1..var_array.count
 loop
  dbms_output.put_line( var_array(i).id ||'    '|| var_array(i).TYPE );
 end loop;

end;

You can use as :

SELECT ... ... 
  FROM myTable 
 WHERE (myTable.id, myTable.type) MEMBER OF var_array ;