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

roger wrote:

>>>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?
>
>
>

I think you should answer the question I asked you. My sense is that you are trying to do something simple in the most convoluted manner possible.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)