Re: Passing an "in" string to a Stored Procedure

On Tue, 07 Apr 1998 22:47:19 -0500, Paul Dudley <dudley_at_ionet.net> wrote:

>I have a stored procedure that I would like to pass a parameter to that
>contains a list of numbers to be used as part of an "IN" for the Where
>clause.
>
>SELECT * FROM table
>WHERE id IN ( idlist)
>
>idlist would be a string such as "1,2,3,4"
>
>The problem is that the id field is a NUMBER in the database and the
>value passed in is a string. If only one value is passed in, an
>implicit conversion is done, but if more than one is provided, I get an
>error message indicating it is an invalid number.
>
>Anyone have any suggestions?
>

SELECT * FROM table
WHERE id IN idlist;

idlist is defined as a varchar2 of whichever size you choose and it is assigned as follows:

idlist:='(1,2,3,4)';

I've tried this in SQL*Plus against a table with a number field and it worked fine.

Hope this helps,

Nuno Guerreiro Received on Wed Apr 08 1998 - 00:00:00 CDT