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

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?

I have this problem about a year ago, and found no decision . However I didn't want a string. My goal was something like ...key in (select key_value from idlist); It was not possible, so I used another very ugly approach .

My opinion is Oracle should do something about it;

I also heard that there is a function that performs the conversion you want. Unfortunately I remember neither source no details of this information  

-- 
Sincerely Yours, Konstantin Kivi, Russia, konst_at_sirena.rinet.ru 
    aka <k-kivi_at_usa.net>, 2:5020/457.24_at_fidonet.org