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

From: roger <rsr_at_rogerware.com>
Date: Mon, 08 Sep 2003 04:24:00 GMT
Message-ID: <Xns93EFDA68E6123rsrrogerwarecom@216.148.227.77>

>>What I still don't see is how to use cast in order to allow
>>me to use the collection variable with the IN operator in
>>the where clause:
>>
>> where col IN (collection_variable)
>>
>>I'm guessing it would be
>>
>> where col IN ( cast(collection_variable as something) )
>>
>>but what to use for the "something" part???
>>

Well, the answer to my question seems to be to do something like this:

  where col in (select column_value from table(collection_variable) )

This at least compiles.

However, when I attempt to execute the procedure, I get the following error
ORA-22905: cannot access rows from a non-nested table item

I'm invoking the function like so, from sqlplus

  SQL> exec cproc( numtab(51,52) );

Here is my current type and procedure definition:

   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? Received on Sun Sep 07 2003 - 23:24:00 CDT