Split variable and then execute query in oracle-CodePudding

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. Please help

CodePudding user response：

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
                );