Re: Using a PLSQL Table with an IN clause

i believe Tom Kyte has answered this question several times. check asktom.oracle.com

Robert Bowen wrote:

> I am trying to do a pretty complicated SELECT using a dynamic IN
> clause. The list of numbers in that IN clause could be anywhere from 0
> to 5000! My problem is *HOW* exactly to do it or more specifically,
> which data type to use.
>
> I tried using a string of numbers seperated by commas but it doesn't
> work. In other words:
>
> ... TABLE.ID IN ('22,34,68')
>
> fails. It's strange. With 2 numbers it gives no error. It doesn't
> return the correct result (I think it's looking in the WHOLE string at
> once, '22,34' instead of first '22' then '34') but with 3 it doesn't
> compile.
>
> I then tried using VARRAYS but I read somewhere that it won't work. So
> I am now trying to use tables but I am a bit stuck as to how to use
> them with an IN clause. I am trying the following:
>
> ... AND (TABLE.ID IN (v_munisTable) ) AND
>
> ... and when I try to compile it gives me a 'disparity of types'
> error. Somehow I need it to look INSIDE the table at each number
> contained within. How can I do this? I found a message from 1999
> explaining how to do it:
>
> http://groups.google.com/groups?q=oracle+plsql+function+return+varray&hl=en&selm=2qEyOBItl2E6jpw6Dkl37g7py%2BwV%404ax.com&rnum=3
>
> ... but I can't make any sense out of it. Can someone throw me a
> lifeline?
>
> Many thanks in advance, or by western union, your choice.
> Bob
Received on Mon Apr 01 2002 - 19:05:47 CST