PL/SQL – Use “List” Variable in Where In Clause

Create the SQL type like this:

CREATE TYPE MyListOfValuesType AS TABLE OF VARCHAR2(4000);

And then use it in a SQL statement

DECLARE
  MyListOfValues MyListOfValuesType;
BEGIN
  MyListOfValues := MyListOfValuesType('MyValue1', 'MyValue2');

  FOR rec IN (
    SELECT *
    FROM DatabaseTable
    WHERE DatabaseTable.Field in (
      SELECT * FROM TABLE(MyListOfValues)
    )
  )
  LOOP
    ...
  END LOOP;
END;

Up until Oracle 11g, this only works with a SQL TABLE type, not with a PL/SQL TABLE type. With Oracle 12c, you could also use PL/SQL types.