PL/SQL - Use "List" Variable in Where In Clause

Question

In PL/SQL, how do I declare variable MyListOfValues that contains multiple values (MyValue1, MyValue2, etc.)

SELECT * 
FROM DatabaseTable 
WHERE DatabaseTable.Field in MyListOfValues

I am using Oracle SQL Developer

Answer 1

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.