Split variable and then execute query in Oracle

Question

I have a IN parameter for P_R4GSTATE where I am getting values for the procedure as "Rajasthan,Maharashtra,Haryana"

And the procedure is below:

PROCEDURE  GET_VENDOR_INFO

(
    PVENDOR_NAME IN NVARCHAR2,
    P_R4GSTATE IN NVARCHAR2,
    P_OUTVENDOR OUT SYS_REFCURSOR
)

AS

BEGIN 

OPEN P_OUTVENDOR FOR
SELECT  * FROM IPCOLO_IPFEE_CALC_MST WHERE CIRCLE=P_R4GSTATE;

END IF;

END GET_VENDOR_INFO;

The issue is that data might be their in any one of those State in the table, how to execute and check for that.

Does this answer your question? [Convert comma separated string to array in PL/SQL](https://stackoverflow.com/questions/3819375/convert-comma-separated-string-to-array-in-pl-sql) — Bryan Dellinger, Jun 14 '22 at 14:49

score 2 · Accepted Answer · answered Jun 14 '22 at 14:51

Split it to rows and use in IN clause:

open p_outvendor for
select  * 
from ipcolo_ipfee_calc_mst 
where circle in (select regexp_substr(p_r4gstate, '[^,]+', 1, level)
                 from dual
                 connect by level <= regexp_count(p_r4gstate, ',') + 1
                );

Split variable and then execute query in Oracle

1 Answers1