Input parameters in procedure

Tell me how to solve such a case:

There is a predicate in the stored procedure that searches for input_param by the input parameter:

procedure tst (input_param in varchar2)
is
    select
        so_much_columns
    from
        so_much_tables
    where
        value in (input_param);
end tst;

When calling this procedure, I need to pass two or much parameters:

call tst.tst (input_param => 'Tst1, Tst2');

It naturally doesn’t work

Are there any solutions?

Answer

Would this help?

Sample data:

SQL> select deptno, ename from emp where deptno in (10, 20) order by deptno;

    DEPTNO ENAME
---------- ----------
        10 MILLER
        10 CLARK
        10 KING
        20 FORD
        20 ADAMS
        20 JONES
        20 SMITH
        20 SCOTT

8 rows selected.

Procedure:

SQL> create or replace procedure p_test (input_param in varchar2) as
  2    l_cnt number;
  3  begin
  4    select count(*) into l_cnt
  5    from emp e join dept d on e.deptno = d.deptno
  6    where d.deptno in (select regexp_substr(input_param, '[^,]+', 1, level)
  7                       from dual
  8                       connect by level <= regexp_count(input_param, ',') + 1
  9                      );
 10    dbms_output.put_line('Count = ' || l_cnt);
 11  end;
 12  /

Procedure created.

SQL> exec p_test('10, 20');
Count = 8

PL/SQL procedure successfully completed.

SQL>