Oracle : passing dynamic string in query through stored procedure

Question

If i am executing below query, it is giving me 4 correct records.

select SUBSCRIBER_NUM, SUBSCRIBER_STATUS, P_ID
from C_S_FORWARD_INFO
where SUBSCRIBER_NUM IN ('0', '07', '070', '0705', '07052', '070526', '0705262', '07052620') and
      SCP_VER = 1

But When i execute below, it is giving me 0 records. Actually I have to dynamically pass the value in 'IN' part of the query.

I tried below ( VAR_CALLING_NUM = 07052620):

while var1<=len LOOP 
temp1 := SUBSTR(VAR_CALLING_NUM, 1, var1); 
temp1 := concat('''',temp1); 
temp1 := concat(temp1,''''); 
temp6 := temp6 || temp1 || ',' ; 
var1:=var1+1; 
END LOOP; 
temp6 := SUBSTR(temp6, 1,length(temp6)-1);

select SUBSCRIBER_NUM, SUBSCRIBER_STATUS, P_ID from C_S_FORWARD_INFO where SUBSCRIBER_NUM IN ( temp6 ) and SCP_VER = 1 order by length(subscriber_num) desc; 

Why this is givign me 0 records. Am i doing anything wrong, by passing temp6 in query like SUBSCRIBER_NUM IN ( temp6 )

Answer 1

You have a string that looks like "'0', '07', . . .". This is a single string. It is not interpreted as multiple values by the in.

You are going to need to use dynamic SQL to put this in as a list. This requires constructing the SQL and then using execute immediate.

Answer 2

You can use below syntax to convert the values in single string to multiple strings to be valid for IN clause.

(SELECT regexp_substr(YOUR_INPUT_VAR,'[^,]+', 1, level) AS list FROM dual CONNECT BY regexp_substr(YOUR_INPUT_VAR, '[^,]+', 1, level) IS NOT NULL)