Pass Comma Separated String to IN clause in stored procedure

Question

I am having comma seperated String and i am passing this comma seperated to oracle stored procedure. Now how to handle this string in cursor to IN clause.

String codes = "ABCD','EEFF','GGHH";

And cursor query is like

    CURSOR cur_bill_cod
   IS
      SELECT a.*
  FROM bill_codes a
 WHERE a.code in (p_codes)  ;

I declared p_codes as varchar in procedure.

But it is not fetching records. I tried string passing in another way as

String codes = "ABCD,EEFF,GGHH";

But in both the ways it failing and it is not throwing any exception. Could you please help me out on this.

Answer 1

Try to use:

CURSOR cur_bill_cod
   IS
      SELECT a.*
  FROM bill_codes a
 WHERE a.code in (  
            SELECT CAST (TRIM (REGEXP_SUBSTR (p_codes,
                                '[^,]+',
                                1,
                                LEVEL)) AS VARCHAR2 (30))
                    codes
                FROM DUAL
            CONNECT BY LEVEL <= REGEXP_COUNT (p_codes, ',') + 1)