Re: Using a PLSQL Table with an IN clause

Yea, I was looking around his site. In fact, the link I pasted below was posted by him. I am sure HE understands how to do it but I have found 2 articles on his site that are *kind of* what I want to do, but neither of them use an IN clause and both of them confuse the bejeezus out of me. (One is called "How do I return the values from a PL/SQL table into a ref cursor?" and the other "How to use varray type in where_clause of select statements?")

... Would you happen to have a simple, clear example of how to do this, or know of a site that does? I have been looking on google, technet ... Bupkus.

Thanks!
Bob

stinky <stinky_at_stunk.org> wrote in message news:<3CA903EA.F94E45C8_at_stunk.org>...
> 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 Tue Apr 02 2002 - 08:54:21 CST