Query with comma seperated IN parameters in PLSQL

Question

I wrote the following in Oracle 11g to separate IN params (I_PRODUCT)and make a query. When I give one parameter as the i_PRODUCT, it is populating some results. When I am inserting multiple comma separated parameters, I am expecting to query one by one and populate the entire result and it is not working.

create or replace PROCEDURE RQUERY 
(
 I_PRODUCT VARCHAR2
, O_Cursor OUT SYS_REFCURSOR
) AS BEGIN
O_Cursor := NULL;
OPEN O_Cursor for

SELECT * FROM Table1 WHERE   
Table1.PRODUCT LIKE ( select regexp_substr(I_PRODUCT,'[^,]+', 1, level) from dual
                     connect by regexp_substr(I_PRODUCT, '[^,]+', 1, level) is not null);

END RQUERY ;

score 1 · Accepted Answer · edited May 23 '17 at 12:03

1

SELECT * FROM Table1 WHERE   
Table1.PRODUCT LIKE ( select regexp_substr(I_PRODUCT,'[^,]+', 1, level) from dual
                     connect by regexp_substr(I_PRODUCT, '[^,]+', 1, level) is not null);

The LIKE operator would fail in above query. You need to use IN, which would internally be evaluated as multiple OR.

Table1.PRODUCT IN ( select regexp_substr(I_PRODUCT,'[^,]+', 1, level) from dual
                     connect by regexp_substr(I_PRODUCT, '[^,]+', 1, level) is not null);

Also, regarding varying IN list, have a look at my answer here.

Update

Based on OP's comments.

Modify the query as:

WITH str_search AS
(
     select regexp_substr(I_PRODUCT,'[^,]+', 1, level) pattern from dual
     connect by regexp_substr(I_PRODUCT, '[^,]+', 1, level) is not null
)
SELECT   *
FROM     Table1
WHERE     EXISTS (
          SELECT     NULL
          FROM     str_search
          WHERE     tab.col     LIKE pattern
                 );

edited May 23 '17 at 12:03

Community

1
1

answered Oct 14 '15 at 13:39

Lalit Kumar B

47,486
13
97
124

I replaced with `LIKE`. No output. I have the PRODUCT Column with values `RETAIL` and `LEASE`. The IN params I'm using to query is `'R%,L%'` – SamK Oct 14 '15 at 13:47
No, it won't work. See my answer int he link provided. Varying IN list doesn't work in that way. Moreover, you are doing a wild search using LIKE using comma separated values. It will not work. – Lalit Kumar B Oct 14 '15 at 13:52
Based on your updated code, if I need to use this for multiple IN comma Param quires, do you recommend to create a function and call from there for each parameter (Like `PRODUCT(R%, L%, MODEL(A,B,C), TYPE(G,H,I)'? – SamK Oct 14 '15 at 15:23
Your updated code is perfect. How to make a common function for multiple queries like this? – SamK Oct 14 '15 at 15:57
@KodS You can create a function, however, you will need to (ab)use EXECUTE IMMEDIATE, as you must do it as dynamic sql where the pattern would come as IN parameter to the function. – Lalit Kumar B Oct 15 '15 at 14:27
Yes, I'm trying to use your solution for at least 3 query parameters. I don't know how to copy and use same solution for three params and that is why I am looking to convert this into a function. – SamK Oct 15 '15 at 14:31
Post a new question, clearly mention what you are trying to do . Post your try so far, I am sure you will get the help required. It is too late at night in my timezone, I would get back tomorrow. – Lalit Kumar B Oct 15 '15 at 14:39
http://stackoverflow.com/questions/33151908/comma-seperated-query-for-multiple-parametes-in-plsql – SamK Oct 15 '15 at 16:01
Ok, I will try. Good night :-) – Lalit Kumar B Oct 15 '15 at 16:06

score 1 · Answer 2 · answered Oct 14 '15 at 15:08

1

 create or replace PROCEDURE ProcedureName
 (
     I_PRODUCT IN VARCHAR2 :=NULL,
     O_Cursor OUT SYS_REFCURSOR
 ) 
 AS
 BEGIN
     DECLARE I_PRODUCT_    VARCHAR2(1000);
 BEGIN
       I_PRODUCT_ := ',' || nvl(I_PRODUCT,'') || ',';
       Open  O_Cursor FOR
       SELECT * FROM Table1    WHERE 1=1
       And (I_PRODUCT_ = ',,' Or I_PRODUCT_ Like '%,' || I_PRODUCT  ||',%');
  END;
 END ProcedureName;

answered Oct 14 '15 at 15:08

pradip vaghani

182
5

I have the PRODUCT Column with values RETAIL and LEASE. The IN params I'm using to query is `R%,L%`. It may query `R%` or `L%` or `R%,L%`. I think your code query all of them, right? – SamK Oct 14 '15 at 18:15

score 0 · Answer 3 · answered Oct 14 '15 at 13:39

0

use IN instead of like;

Table1.PRODUCT IN( 
    select regexp_substr(I_PRODUCT,'[^,]+', 1, level) from dual
    connect by regexp_substr(I_PRODUCT, '[^,]+', 1, level) is not null
)

answered Oct 14 '15 at 13:39

Praveen

8,945
4
31
49

Query with comma seperated IN parameters in PLSQL

3 Answers3

Linked