Split comma separated string and pass to IN Clause in Oracle

--Step 1 :- Create table

CREATE TABLE TEST

(

  ID INTEGER,

  NAME VARCHAR2(50)

);

--Step 2 :- Insert records in table

INSERT INTO TEST VALUES('1','Ram');

INSERT INTO TEST VALUES('2','Shyam')

INSERT INTO TEST VALUES('3','Ghanshyam');

--Step 3 :- Select records from table

SELECT * FROM "TEST" t

ID	NAME
1	Ram
2	Shyam
3	Ghanshyam

--Step 4 :- Create dummy table

CREATE TABLE DUMMY

(

  ID INTEGER

);

Let's Test It...

-- Solution 1

DECLARE

  -- Comma seperated value

  V_INPUT VARCHAR2(50) := '1,2,3';

  V_COUNT INTEGER;

  V_ARRAY DBMS_UTILITY.LNAME_ARRAY;

BEGIN

  DBMS_UTILITY.COMMA_TO_TABLE(LIST   => REGEXP_REPLACE(V_INPUT,

                                                       '(^|,)',

                                                       '\1x'),

                              TABLEN => V_COUNT,

                              TAB    => V_ARRAY);

  FOR I IN 1 .. V_COUNT LOOP

    -- Insert splited value in DUMMY table

    INSERT INTO DUMMY ("ID") VALUES (SUBSTR(V_ARRAY(I), 2));

  END LOOP;

END;

--Use DUMMY table records in IN Clause

SELECT * FROM TEST WHERE ID IN (SELECT ID FROM DUMMY);

ID	NAME
1	Ram
2	Shyam
3	Ghanshyam

-- Solution 2

SELECT *

  FROM TEST

 WHERE ID IN

       (SELECT REGEXP_SUBSTR('1,2,3', '[^,]+', 1, LEVEL) "Result"

          FROM dual       

        CONNECT BY REGEXP_SUBSTR('1,2,3', '[^,]+', 1, LEVEL) IS NOT NULL);

ID	NAME
1	Ram
2	Shyam
3	Ghanshyam