Can i pass multiple parameters to a bind variable in a SQL query in Oracle with equal to sign?

Question

The user will select multiple pick value from screen like 100, 101, 102 etc. and those values need to be passed to the below query and generate the output for further processing.

How can I pass multiple values to a bind parameter in a query having a equal to sign?

SELECT tab1.PICKLIST_KEY icrggq_0, NULL icrggq_1, 0 icrggq_2
  FROM tab1
 WHERE dc_code = 942
   AND tab1.pick_value = :p_pick_value;

There are hundreds of queries like this and we will not be able to modify all of them manually. Is it possible to be done by plsql procedure or function ? We are receiving the multiple values through front end Oracle Forms screen.

Answer 1

You can't (not that way, that is), but you could "split" those comma-separated values into rows and then use them as a subquery. Something like this:

SELECT tab1.picklist_key icrggq_0, NULL icrggq_1, 0 icrggq_2
  FROM tab1
 WHERE     dc_code = 942
       AND tab1.pick_value IN
              (    SELECT REGEXP_SUBSTR ( :p_pick_value, '[^,]+', 1, LEVEL)
                     FROM DUAL
               CONNECT BY REGEXP_COUNT ( :p_pic_value, ',') + 1)

Answer 2

How can I pass multiple values to a bind parameter in a query having a equal to sign?

You cannot pass multiple values and use then in a query with an equals sign to match any one of the list of values.

A bind variable is a single value so you can concatenate multiple strings into a single delimited string and pass that single string but then you will either need to match the entire delimited string or change the query to perform sub-string matches.

Or, you can concatenate multiple values into a single collection and pass that single collection; however, this is not supported by all client applications (i.e. C# allows passing PL/SQL associative array collections but not nested table collections, Java/JDBC allows passing nested table collections but not associative arrays, SQL/Plus does not allow either when defining a bind VARIABLE). Even then, the equals operator would match the entire collection and not one element of the collection.

There are hundreds of queries like this and we will not be able to modify all of them manually.

If you are changing the input then you will need to change them all.

---

Option 1: LIKE operator:

You do not need to split the values. You can use the LIKE operator to match one element of a comma-delimited string:

SELECT PICKLIST_KEY AS icrggq_0,
       NULL AS icrggq_1,
       0 AS icrggq_2
FROM   tab1
WHERE  dc_code = 942
AND    ',' || :p_pick_value || ',' LIKE '%,' || pick_value || ',%';

---

Option 2: Collections

Depending on the client application you are using to connect to the database, you may be able to pass in a collection as the bind value and use the MEMBER OF operator:

SELECT PICKLIST_KEY AS icrggq_0,
       NULL AS icrggq_1,
       0 AS icrggq_2
FROM   tab1
WHERE  dc_code = 942
AND    pick_value MEMBER OF :p_pick_value;

A Java example of passing a collection is here.