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 — Bryan Dellinger
– Bryan Dellinger, Commented Jun 14, 2022 at 14:49

Littlefoot · Accepted Answer · 2022-06-14 14:51:46Z

2

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

answered Jun 14, 2022 at 14:51

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Split variable and then execute query in Oracle

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related