Pass Multivalue in WHERE IN ORACLE

Question

I need you help to pass a multivalue to a stored procedure in Oracle,

The values are created dynamically.

Basically I'm sending the values from .NET in a simple "string" and Oracle receives it as varchar2:

Stored Procedure

 create or replace procedure sp_text_in
     (text varchar2)
 AS
     CURSOR texts
     IS
         SELECT text FROM t_text where key_text in (text)
         ;
     BEGIN
         FOR reg IN texts LOOP
           dbms_output.put_line(reg.text);
     END LOOP;

 end sp_text_in;

Example: the values can be:

 select * from t_text where key_text in (197,198,196);

OR simply one value

 select * from t_text where key_text in (197);

this might be helpful: https://stackoverflow.com/questions/8848186/passing-an-array-of-strings-to-a-procedure-and-using-it-in-the-where-clause-with — jose_bacoy, Mar 12 '18 at 17:28
Seems a bit unfair on the database to dump an unformatted string on it and make it parse it before getting on with the actual query. There are various ways to convert csv strings into arrays in PL/SQL but ideally you would pass the right type in the first place. — William Robertson, Mar 12 '18 at 19:01

score 1 · Answer 1 · answered Mar 12 '18 at 18:21

Ideal option is as per the link shared by @anonyXmous. Another sub optimal option is using dynamic SQL. But this could allow SQL injection. Dynamic SQL solution below.

create or replace procedure SP_TEXT_IN (TEXT varchar2)
as

  type TEXTSTABTYP is table of varchar2(1000);
  TEXTSTAB TEXTSTABTYP := TEXTSTABTYP();

begin

  execute immediate 'select TEXT from T_TEXT where KEY_TEXT in ('||TEXT||')'
  bulk collect into TEXTSTAB;

  for IDX in TEXTSTAB.first..TEXTSTAB.last 
  loop

    dbms_output.put_line(TEXTSTAB(IDX));

  end loop;

end sp_text_in;

Pass Multivalue in WHERE IN ORACLE

1 Answers1