CODEWITHSUNDEEP
javastored-procedures
Sarvesh V
Sarvesh V

Reputation: 157

Pass Comma Separated String to IN clause in stored procedure

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.

Upvotes: 3

Views: 2438

Answers (1)

IRomanov
IRomanov

Reputation: 42

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)

Upvotes: 2

Related Questions