Passing a string in an IN operator in oracle

[Kans Singh]

Hi All

I want to have a string in an IN condition.I have the below code it does not return any ID.
For Example...

XSTRING_COURSES_IN = |123,456,789|
CREATE OR REPLACE PROCEDURE SCP_MOVE_TEMP ( XSTRING_COURSES_IN VARCHAR2)
AS
BEGIN
XSTRING_COURSES :=  REPLACE(REPLACE(XSTRING_COURSES_IN,',',''','''),'|','''');
SELECT ID into XID FROM OFFER_TEMP LET WHERE offer_temp_no in (XSTRING_COURSES_IN)

......
offer_temp_no is varchar2


Waiting for any answer.............

Kanchan

[Agador Paloi]

if offer_temp_no is a number your code wont work. The replace function returns a string, so the in compare wont work in your select. What are you trying to accomplish in your replace?


Cheers,
Agador

[John Bengler]

Hi Kans,

I don't know a way to really use such a varchar parameter as an IN criterion, but if performance (the use of indexes) doesn't matter too much here is a workaround:

 
   XSTRING_COURSES_IN = |123,456,789|  
   CREATE OR REPLACE PROCEDURE SCP_MOVE_TEMP ( XSTRING_COURSES_IN VARCHAR2)  
   AS  
   BEGIN  
-- doesn't matter for this code   XSTRING_COURSES :=  REPLACE(REPLACE(XSTRING_COURSES_IN,',',''','''),'|',''''); 
   XSTRING_COURSES_IN := ','||XSTRING_COURSES_IN||','; -- just add a comma at the start and at the end of the provided list 
   SELECT ID into XID FROM OFFER_TEMP LET WHERE XSTRING_COURSES_IN like '%,'||offer_temp_no||',%';

   

But I think if the XSTRING_COURSES_IN makes sense your code (and my example,too) won't work, because for a list of course numbers the query SELECT ID into XID FROM OFFER_TEMP .. will return multiple rows..?


Did this help?

John