How to Pass Comma Separated Values to IN Operator in Oracle (Basic 1 - 27)

The IN operator in Oracle is used to check if a value is present in a list of values. The list of values can be specified explicitly, or it can be the result of a subquery.

However, the IN operator cannot be used to compare a value to a comma-separated string. To pass comma-separated values to the IN operator, you need to use the REGEXP_SUBSTR function.

Example

The following example shows how to use the REGEXP_SUBSTR function to pass comma-separated values to the IN operator:

SQL

SELECT *
FROM employees
WHERE ename IN (
  SELECT REGEXP_SUBSTR('Ravi,Ankit,Pritesh,Ram,Kishna', '[^,]+', 1, level)
  FROM dual
  CONNECT BY REGEXP_SUBSTR('Ravi,Ankit,Pritesh,Ram,Kishna', '[^,]+', 1, level) IS NOT NULL
);

This query will return all employees with the names Ravi, Ankit, Pritesh, Ram, and Kishna.

Passing Comma Separated Values to a Procedure

If you need to pass comma-separated values to a procedure, you can use the following steps:

1. Create a cursor that selects the comma-separated values from a dual table.
2. Use the REGEXP_SUBSTR function to split the comma-separated values into individual rows.
3. Use the cursor to iterate over the individual rows and pass them to the procedure.

The following example shows a procedure that takes a comma-separated string as input and prints the names of the employees with those names:

SQL

CREATE OR REPLACE PROCEDURE GetDetails(in_myString IN VARCHAR2)
AS
  CURSOR C1 IS
    SELECT ename
    FROM employees
    WHERE ename IN (
      SELECT REGEXP_SUBSTR(in_myString, '[^,]+', 1, level)
      FROM dual
      CONNECT BY REGEXP_SUBSTR(in_myString, '[^,]+', 1, level) IS NOT NULL
    );
BEGIN
  FOR rec IN C1 LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ' || rec.ename);
  END LOOP;
END;

To execute the procedure, you can use the following statement:

SQL

BEGIN
  GetDetails('Ravi,Ankit,Pritesh,Ram,Kishna');
END;

This will print the following output to the console:

EMPLOYEE Ravi
EMPLOYEE Ankit
EMPLOYEE Pritesh
EMPLOYEE Ram
EMPLOYEE Kishna

Conclusion

The REGEXP_SUBSTR function can be used to pass comma-separated values to the IN operator in Oracle. This can be useful for querying data and passing data to procedures.