Re: select ... where col in (collection)

> create or replace type numtab table of number;
> /
>
> create or replace procedure cproc (cvar numtab)
> is
> begin
> for c in
> (select * from all_users where user_id in
> (select column_value from table(cvar) ))
> loop
> dbms_output.put_line(c.username);
> end loop;
> end;
> /
>
>
> Am I getting warmer or colder?
 

Try this:

create or replace type numtab is table of number; /

create or replace procedure cproc (cvar numtab) is
begin
  for c in
   (select * from all_users,
     table (cast(cvar as numtab)) where user_id = column_value)   loop

     dbms_output.put_line(c.username);
  end loop;
end;
/

exec cproc(numbtab(11,12,13,14));

This worked for me on 9.2.

hth,
Rene Nyffenegger

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch