How To Pass Multiple Values (IN Clause Query) in Stored Procedure of Oracle DB

 

What exactly IN Clause do in Query?

First, We create temporary table with data:

1 2 3 4 5

create table temp_tab(col1 varchar2(100)); insert into temp_tab values('3456'); insert into temp_tab values('5678'); insert into temp_tab values('1234'); select * from temp_tab where col1 IN ('3456','1234');

COL1

3456

1234

As, Observed give the value which exist in table based on value pass multiple way in Where clause.

Did you ever try same query using Stored Procedure? It will not Work as

 For Eg:

1 2 3 4 5 6 7

create or replace procedure sp_temp (p1 in varchar2) as val varchar2(10); begin  select col1 into val from temp_tab where col1 in (p1);  dbms_output.put_line(val); end;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

exec sp_temp ('1234'); Result: 1234   But, What about multiple value pass? exec sp_temp('1234','3456'); Error:   ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'SP_TEMP'   Means:We can't pass two value as we have one parameter in sp_temp   But, What about multiple value pass in one parameter? exec sp_temp('1234,3456'); Error: ORA-01403: no data found ORA-06512   Means, There is no data exist in our table 1234,3456

How we achieve to understand passing multiple value in parameter and retrieve from table as like simple IN Clause.

Hint:

For that, require to use dynamic SQL in stored procedure

Result:

1 2 3 4 5

exec sp_temp ('1234,3456');   Statement processed. 3456 1234

Solution:

For that, require to request on below button. You will receive solution on same day with session.

REQUEST NOW