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 )

You have a loop code there so it is PL/SQL. In PL/SQL you can do select into some variable but not just select. Can you post whole your code please or just describe what do you want to do in PL/SQL? — Kacper
– Kacper, Commented Nov 20, 2016 at 13:54

Gordon Linoff · Accepted Answer · 2016-11-20 13:56:44Z

2

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.

answered Nov 20, 2016 at 13:56

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

VJS Over a year ago

Thanks for your comment. Actully I have a java code which is calling stored procedure. Now Stored procedure is so big, I have just shown some part of it here. Can you tell me how do i do it.

VJS Over a year ago

Is it possible it store seperate values in variables. Like a1='07', a2='070', a3='0705', a4='07052' and do select SUBSCRIBER_NUM, SUBSCRIBER_STATUS, P_ID from C_S_FORWARD_INFO where SUBSCRIBER_NUM IN ( a1, a2, a3, a4, a5, a6, a7, a8) and SCP_VER = 1 order by length(subscriber_num) desc;

VJS Over a year ago

Your help is requested sir

Gordon Linoff Over a year ago

@VJS . . . Yes, you can put each item in the IN list in as a separate parameter.

VJS Over a year ago

Thanks for your help.

AnkitK · Accepted Answer · 2023-03-08 09:31:43Z

0

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)

answered Mar 8, 2023 at 9:31

AnkitK

5085 silver badges11 bronze badges

Collectives™ on Stack Overflow

Oracle : passing dynamic string in query through stored procedure

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related