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

Hi Paul,

I think the main problem is that you can't use lexical substitution directly in a PLSQL procedure.

eg you can't pass in in_list and then have a statement of select * from thing where column in (in_list) you can do it in SQLPLUS because you can use substitution variables.

What you could do would be to use DBMS_SQL to execute you're statement dynamically, then you could build in the passed in-list.

In this way, the fact that the value in is a string but your id is a number doesn't matter. Even so if the lexical substitution could have worked you would only have needed to put quotes around your ids in the in-list and let implicit char to number conversion do the rest.

eg

select * from thing
where number_id in ('1','2','3')

cheers

Rod

Lane Associates
Lane_Associates_at_Compuserve.com
http://www.Lane-Associates.com

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.
>
> Anyone have any suggestions?
Received on Thu Apr 16 1998 - 00:00:00 CDT