0

I am passing an input value as 'MI,NOKIA,APPLE' to a procedure. This input value has to be passed to the query in where condition.

For example:

create or replace procedure abc123(p_name varchar2)
is
v_val number;
begin
 dbms_output.put_line ('p_name: '||p_name );

 Select 1 
 Into v_val
 from MYTABLE
 where Model in p_name;

 dbms_output.put_line ('v_val: '||v_val );
end;

This is not working. How do I pass the multiple input values as single parameter?

braaterAfrikaaner
  • 1,072
  • 10
  • 20
syam
  • 1
  • This is known as "varying in-lists". The solution depends on whether you can change the interface to pass in a collection, which is how SQL and PL/SQL handle multi-valued variables like this. If not, you will have to parse the string value, and there are [various ways to do this](http://www.williamrobertson.net/documents/comma-separated.html). – William Robertson Feb 01 '18 at 17:49

2 Answers2

0

Use paranthesis after IN operator :

Select 1 Into v_val from MYTABLE where Model in (p_name);
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
0

try below code,

create or replace procedure proc123 (p_param VARCHAR2)
IS
     TYPE cur_typ IS REF CURSOR;
     c cur_typ;
     v_query VARCHAR2(200) := 'SELECT *  '||
                               'FROM (SELECT ''Model1'' model FROM dual '||
                               'union all '||
                               'SELECT ''Model2'' FROM dual '||
                               'union all '||
                               'SELECT ''Model3'' FROM dual)';
      v_model VARCHAR2(20);
BEGIN
     v_query := v_query||' WHERE INSTR('''||p_param||''',model) > 0';
     dbms_output.put_line(v_query);
     OPEN c FOR v_query;
     LOOP
         FETCH c INTO v_model;
         EXIT WHEN c%NOTFOUND;
         dbms_output.put_line(v_model);
     END LOOP;
     CLOSE c;
END;
/

--set your dbms_output on
begin
    proc123('Model1, Model2');
end;
/

if you want to pass the parameter in a query, then you can use the INSTR function

 Select 1 
   from MYTABLE 
  where INSTR(p_name, model) > 0;
eifla001
  • 1,137
  • 8
  • 8