string in an WHERE IN predicate 1

[cappmgr]

I have a procedure that now has to take more than one PackageId so I thought I could use an IN in the WHERE perdicate. It dies with an invalid number. If I pass one PackageId it works if I pass more than one it does not. It is being passed from Crystal Reports which puts single ticks around the input. So I thought Trim Replace would work but it does not. Any idea's? I was thinking that if I can't get this to work I will try splittig the string and use a loop but would like to see this work. For proof of concept I did the following

1 select Trim(Replace('2314,2315,2318,4456','''''','')) tr
2* from dual

output

TR
-------------------
2314,2315,2318,4456

the procedure has this in it
l_PackageId := Trim(Replace(p_i_packageid,'''''',''));

then in the WHERE predicate

WHERE pkg.package_id IN (l_packageid)

pkg.package_id is a NUMBER
any help appreciated.
Marty

 

[LKBrwnDBA]

There are many ways to solve this problem, but the easiest is using the "INSTR()" function:

WHERE INSTR(','||l_packageid||',', ','||pkg.package_id||',') > 0



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[cappmgr]

Mr Brown,
I thank you!
Marty