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

Paul Dudley 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.

Why not

	SELECT * FROM table
	WHERE TO_CHAR(id) IN (idlist);

Worked when I tried it.

Sj

Oracle Developer Extraordinaire :-) Received on Wed Apr 08 1998 - 00:00:00 CDT