How to pass multiple values into one parameter in stored procedure (Oracle)

Question

I have a parameter created for a stored procedure looking to allow user to pass in multiple inputs.

create procedure sp1 (p1 in varchar2)
as
begin
 select proc_id from proc_tbl where proc_id in (p1);
end;

The user expects to input multiple values separate by comma or space such as a1, b2, c3 in p1. All the PROC_ID stored in proc_tbl are in upper case.

The sp didn't run successfully and take in the inputs as a whole string.

In normal sql in clause we can just type out like this

select proc_id from proc_tbl where proc_id in ('A1', 'B2', 'C3')

How can we apply the same logic in oracle sp without case sensitivity?

Answer 1

From the database perspective, the csv list that are giving as argument is just another string, not a list of values. So you can't use IN.

One simple approach uses a regex function:

select proc_id from proc_tbl where regexp_like(p1, '^|,' || proc_id || ',|$', 'i');

The regex means:

• proc_id is at the beginning of the parameter string (^) or is preceded by a comma
• AND: proc_id is at the end of parameter string ($) or is followed by a comma

Answer 2

From a SQL injection standpoint, you probably want to take some type of collection as a parameter. Or at least scan the input for reserve words. But after that you could just use your string as part of dynamic SQL like this:

create procedure sp1 (p1 in varchar2)
as
  TYPE lt_ResultTable is TABLE OF proc_table.proc_id%TYPE;
  l_tResult lt_ResultTable;
  l_nResult proc_table.proc_id%type;
begin
 EXECUTE IMMEDIATE 'select proc_id from proc_tbl where proc_id in ('||p1||')'
  BULK COLLECT INTO l_tResult;
 /*If you are guaranteed only one row will return from your query, you don't need to use a 
  nested table 
 EXECUTE IMMEDIATE 'select proc_id from proc_tbl where proc_id in ('||p1||')' INTO l_nResult;
*/
end;

Answer 3

Simple select without INTO clause would not even compile. However, there is a way to safely pass several values in one argument:

create or replace procedure sp1(p1 in varchar2) is
begin
for r in (
    select xt.proc_id
    from proc_tbl pt, xmltable(p1 columns proc_id varchar2(100) path '.') xt
    where pt.proc_id = xt.proc_id
)
loop
    dbms_output.put_line(r.proc_id);
end loop;
end;
/

Answer 4

If you have control over the parameters, it would be more robust to use a collection instead of a comma-separated list in a string. You can create your own collection or use one of the pre-built collections.

Schema

--drop table proc_tbl;
create table proc_tbl(proc_id number);
insert into proc_tbl values(1);
insert into proc_tbl values(2);
insert into proc_tbl values(3);

Procedure

--SYS.ODCINUMBERLIST is defind as a VARRAY(32767) OF NUMBER.
--Or you could create your own type, like: create type number_tab as table of number;
create or replace procedure sp1 (p1 in sys.odcinumberlist) as
    v_count number;
begin
    select count(*)
    into v_count
    from proc_tbl
    where proc_id in (select column_value from table(p1));

    dbms_output.put_line('Count: '||v_count);
end;
/

Sample Call

begin
    sp1(sys.odcinumberlist(1,2));
end;
/

Result
------
Count: 2