select ... where col in (collection)

I wonder if I'm missing something obvious, or if I'm trying to do the impossible...

What I want is, within PL/SQL, to use a collection with the IN operator in an SQL statement.

Something like so...

  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 where user_id in (cvar) )     loop

• one row from the set of id values in cvar... end loop; end;
  /
  

But compiling cproc gives
  PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got RSR.NUMTAB

Should I be able to do this kind of thing or not? If so, what is the magic syntax I'm missing?

Thanks for your help and patience.

roger Received on Sun Sep 07 2003 - 16:30:35 CDT