Re: Convert comma delimited numbers to numbers in SQL IN query

On 25 May 2006 08:27:45 -0700, "vinod_agarwal" <vinodagarwal_at_gmail.com> wrote:

>Hi
>I have a qurey like this in a stored procedure.
>SELECT field1, field2 from myTable1 WHERE tablePk IN (100, 200, 300,
>400);
>
>In above query the number of values in IN clause are not known when the
>procedure is invoked from JDBC. Hence the stored procedure takes a
>VARCHAR2 parameter with all the values in a comma delimited form. E.g.:
>"100, 200, 300, 400").
>
>Howevever I cannot do something like this:
>SELECT field1, field2 from myTable1 WHERE tablePk IN
>(myStoreProcParam);
>
>because the param myStoreProcParam is of type VARCHAR2, whereas the
>tablePk is of type NUMBER.
>
>How do I solve this problem?

Go to http://asktom.oracle.com and search for 'dynamic in list'

--
Sybrand Bakker, Senior Oracle DBA