Sending an array of values to Oracle procedure to use in WHERE IN clause

Question

I have a stored procedure in Oracle as shown below:

CREATE PROCEDURE MY_TEST_PROC(
  CUR OUT SYS_REFCURSOR,
  PARAM_THAT_WILL_BE _USED_INSIDE_WHERE_IN
)
AS
BEGIN
  OPEN CUR FOR 
    SELECT * 
      FROM MY_TABLE 
     WHERE COL1 IN (here I want to put values received from C#)
END;

On the ASP.NET application side I have a select element with several options. I want to use these list items in my WHERE clause. I know that I can have a VARCHAR2 input parameter in my stored proc, make a comma separated string from the list items, send it to the procedure. There are two concerns with going this way:

I make my website vulnerable to SQL injections
In my stored proc I have to use EXECUTE ('SELECT ...') pattern which I would like to avoid.

How can I send these list items to the stored procedure and use them inside the WHERE IN clause? I'm using ODP.NET and have heard of UDT but don't know how to use it.

Community · Accepted Answer · 2017-05-23 12:24:54Z

6

One way could be to use a VARRAY for the PARAM_THAT_WILL_BE _USED_INSIDE_WHERE_IN parameter and use it as described here
I'm not sure, though, how to call it from c#.

Another way is to use varchar2 with a csv as you stated in your question but without dynamic sql, like this:

CREATE PROCEDURE MY_TEST_PROC(
  CUR OUT SYS_REFCURSOR,
  PARAM_THAT_WILL_BE varchar2)
AS
BEGIN
  OPEN CUR FOR 
    SELECT * 
      FROM MY_TABLE 
     WHERE COL1 IN (
        select regexp_substr(PARAM_THAT_WILL_BE, '[^,]+',1,level) p
          from dual t
       connect by level <= regexp_count(PARAM_THAT_WILL_BE, ',') + 1
)
END;

edited May 23, 2017 at 12:24

CommunityBot

11 silver badge

answered Nov 27, 2012 at 9:04

A.B.Cade

16.9k1 gold badge40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

valex · Accepted Answer · 2012-11-27 09:18:12Z

2

You can add this comma separated input parameter as a varchar() and use following where statement:

where (','||PARAM_THAT_WILL_BE||',' like '%,'||COL1||',%')

for example if PARAM_THAT_WILL_BE='2,3,4,5' and col1=3 we get:

where (',2,3,4,5,' like '%,3,%')

and it's TRUE if COL1 value is in this list. Here you don't use a dynamic query so you avoid concerns 1) and 2).

answered Nov 27, 2012 at 9:18

valex

24.2k7 gold badges47 silver badges62 bronze badges

2 Comments

A.B.Cade Over a year ago

A smart approach indeed, though may cost in performance. For example if there is an index on col1, it might not be used

APC Over a year ago

This is not a smart approach. Dynamic SQL should always be a last resort. Also it places constraints on the format (no spaces, no tabs) of what is basically a free text field. And then there's the performance, as AB says.

Rajesh D · Accepted Answer · 2015-12-30 08:28:34Z

1

For this scenario i used like this

CREATE PROCEDURE MY_TEST_PROC(CUR OUT SYS_REFCURSOR,A in VARCHAR2 ) AS BEGIN OPEN CUR FOR SELECT * FROM MY_TABLE WHERE COL1 IN (SELECT REGEXP_SUBSTR(**A**,'[^,]+', 1, LEVEL) FROM DUAL CONNECT BY REGEXP_SUBSTR(**A**, '[^,]+', 1, LEVEL) IS NOT NULL) END;

The A value should contain open and closed qutoes('). EX: '512,456,4564' if it one value '512' like this

answered Dec 30, 2015 at 8:28

Rajesh D

1772 silver badges13 bronze badges

Collectives™ on Stack Overflow

Sending an array of values to Oracle procedure to use in WHERE IN clause

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related