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.

score 0 · Answer 1 · answered Feb 08 '22 at 08:40

0

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)

answered Feb 08 '22 at 08:40

Littlefoot

131,892
15
35
57

There are hundereds 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 receving the multiple values through front end Oracle Forms screen. – Gautam S Feb 08 '22 at 08:49
Thanks for your prompt response. Is there any way this can be automatically done through procedure or function without having to manually change all the queries. – Gautam S Feb 08 '22 at 08:56
Does it mean that those *hundreds of queries* worked OK until recently, when you "allowed" multiple choices to be sent as a parameter? I wouldn't know how to make all of them accept new data format (comma-separated values) without actually modifying their WHERE clauses, manually, one-by-one. That's *bad news*, I guess. Maybe someone else knows how to do it; wait until they respond. Hopefully, someone will. – Littlefoot Feb 08 '22 at 08:59
This is for a report screen where upon selecting the report name and parameters, the report will be downloaded as an excel, text etc. In this screen there is provision to select only one value for each parameter till now. We are introducing the option to select multiple values also now. – Gautam S Feb 08 '22 at 09:16
OK; I still think that there's no *magic* which will make that happen until you fix each of SELECT statements involved. – Littlefoot Feb 08 '22 at 09:18

score 0 · Answer 2 · edited Feb 27 '22 at 22:38

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.

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

2 Answers2

Option 1: LIKE operator:

Option 2: Collections

Option 1: `LIKE` operator: