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.

Answer 2

Use a collection:

CREATE TYPE Varchar2TableType AS TABLE OF VARCHAR2(200);

Or use a built-in type like SYS.ODCIVARCHAR2LIST or SYS.ODCINUMBERLIST:

VARIABLE cursor REFCURSOR;

DECLARE
  your_collection SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
BEGIN
  your_collection.EXTEND( 100 );

  your_collection(  1) := 'Some value';
  your_collection(  2) := 'Some other value';
  -- ...
  your_collection(100) := DBMS_RANDOM.STRING( 'x', 20 );

  OPEN :cursor FOR
  SELECT t.*
  FROM   your_table t
         INNER JOIN
         TABLE( your_collection ) c
         ON t.id = c.COLUMN_VALUE;
END;
/

PRINT cursor;

Answer 3

How about using a WITH clause which basically builds a temp table? Not real reusable. You could use an array or I would argue joining to a lookup table would be better.

WITH MyListOfValues(col1) AS (
  select 'MyValue1' from dual union
  select 'MyValue2' from dual union
  select 'MyValue3' from dual 
)
SELECT * 
FROM DatabaseTable  
WHERE Column in (
  select col1
  from  MyListOfValues);