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

How to retrieve values from cur ref 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

Let's Test it

Step 1:- Create procedure

CREATE OR REPLACE PROCEDURE USP_TEST1(V_OUT OUT pkg_nonpins.CUR_REF) AS

BEGIN

  OPEN V_OUT FOR

    SELECT * FROM TEST;

END;

Step 2:- Create procedure

CREATE OR REPLACE PROCEDURE USP_TEST2 AS

  V_OUT  SYS_REFCURSOR;

  V_ID   INTEGER;

  V_NAME VARCHAR2(20);

BEGIN

     

  USP_TEST1(V_OUT);

  LOOP

    FETCH V_OUT

      INTO V_ID, V_NAME;

    IF V_OUT%NOTFOUND THEN

      EXIT;

    END IF;

    DBMS_OUTPUT.PUT_LINE('ID-' || V_ID || ' NAME-' || V_NAME);

  END LOOP;

  CLOSE V_OUT;

END;

OR

DECLARE

  V_OUT  SYS_REFCURSOR;

  V_ID   INTEGER;

  V_NAME VARCHAR2(20);

BEGIN

  USP_TEST1(V_OUT);

  LOOP

    FETCH V_OUT

      INTO V_ID, V_NAME;

    IF V_OUT%NOTFOUND THEN

      EXIT;

    END IF;

    DBMS_OUTPUT.PUT_LINE('ID-' || V_ID || ' NAME-' || V_NAME);

  END LOOP;

  CLOSE V_OUT;

END;