Can i pass multiple parameters to a bind variable in a sql query in oracle with equal to sign?-CodePudding

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 o/p for further processing. Can some one please tell 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 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.

CodePudding user response：

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)

CodePudding user response：

Can some one please tell 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.

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.

Option 1: LIKE operator:

Option 2: Collections

Option 1: `LIKE` operator: