select where in contents of array 1

[Matsul]

Hi all,

I was wondering if a way of doing


select * from table name where
column in array ();

without looping as below;

for i in 1 .. 100 loop

select * from table name where
column_name=array (i);

end loop;

which would mean retriving the values one by one which is not so good as the select is part of a long cursor.

thanks for any help.

 

[Dagon]

You could do something like this.

drop type t_in_clause
/

create or replace type t_in_clause as table of varchar(500);
/

declare
    type t_curs is ref cursor;
    v_in_clause t_in_clause := t_in_clause();
    v_curs t_curs;
    v_result varchar2(1);
begin
    v_in_clause.extend;
    v_in_clause.extend;
    v_in_clause(1) := 'X';
    v_in_clause(2) := 'Y';
    open v_curs for
       select colval from (select 'X' colval from dual union all select 'Y' from dual)
       where colval in (select column_value from table(cast(v_in_clause as t_in_clause)));
    loop
       fetch v_curs into v_result;
       if v_curs%notfound then 
          exit;
       end if;
       dbms_output.put_line(v_result);
    end loop;
    close v_curs;
end;
/