Using a comma-separated list in an SQL query

SQL has arrays

A frequently-asked Oracle question:

My procedure is passed a comma-separated list of IDs, for example 7369,7499,7839,7902. I tried to use it in my code like this:

DECLARE
	p_empno_list CONSTANT VARCHAR2(20) := '7369,7499,7839,7902';
BEGIN
	FOR r IN (
		SELECT * FROM emp
		WHERE  empno IN (p_empno_list)
		)
	LOOP
		DBMS_OUTPUT.PUT_LINE(RPAD(r.empno,9) || r.ename);
	END LOOP;
END;
/

but it just gives me an error:1

ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 4

Clearly, SQL does not consider

WHERE  empno IN (p_empno_list)

to be equivalent to

WHERE  empno IN (7369,7499,7839,7902)

And of course is cannot, and should not. What if you had a value such as '123, Fake St, Springfield' that represented a single address line? How would you feel if you used v_address_line in a query similar to the one above, and SQL chose to treat it as not one but three values ('123', 'Fake St' and 'Springfield'), just because it happened to contain commas and the query used IN? It would be wrong! SQL has to treat each variable as one value.

What the programmer is really looking for is a different type of variable that explicitly represents a set of values. Fortunately Oracle SQL supports exactly this, in the form of collections.2

You create a scalar collection type3 like this:

CREATE TYPE INTEGER_TT AS TABLE OF INTEGER
/

The name itself is not important except that it should give an indication of what it represents, and the fact that you will have to live with it. I use _TT ("table type") for collection types (see my PL/SQL Coding Standards for more suggestions regarding naming standards). Since it should be a generic type that you will reuse in many different places, it is better to refer to the base datatype, INTEGER, than the fact that right now you want it for an employee ID. While you are at it, why not create a couple more:

CREATE TYPE NUMBER_TT AS TABLE OF NUMBER
/

CREATE TYPE VARCHAR2_TT AS TABLE OF VARCHAR2(4000)
/

Now, when your list of IDs is an INTEGER_TT instead of a plain old character string, you can use it in SQL:

DECLARE
	p_empno_list CONSTANT INTEGER_TT := INTEGER_TT(7369,7499,7839,7902);
BEGIN
	FOR r IN (
		SELECT empno, ename4
		FROM   emp
		WHERE  empno IN
		       ( SELECT column_value5
		         FROM   TABLE(p_empno_list) )
		)
	LOOP
		DBMS_OUTPUT.PUT_LINE(RPAD(r.empno,9) || r.ename);
	END LOOP;
END;
/

7369     SMITH
7499     ALLEN
7839     KING
7902     FORD 

The only problem now is how to get an INTEGER_TT collection into your procedure in place of a character string. The simplest solution is to change your procedure's parameter list so that it is by definition passed one. If an existing application has been built to pass character strings (I can never understand why anyone would do this as it just creates more work, but apparently it happens frequently), here are some suggestions:

• Change the calling procedure so that it calls your procedure as
  YOURPROC(INTEGER_TT(7369,7499,7839,7902));
  instead of
  YOURPROC('7369,7499,7839,7902');
• Create an overloaded version of your procedure that accepts a VARCHAR2 parameter. Within this second version, convert the VARCHAR2 string into an INTEGER_TT collection using a SPLIT() function (you'll have to write your own - an example is here), and call the first version passing the INTEGER_TT collection.
• As a last resort, continue to accept a character string as a parameter to your procedure, but convert it to an INTEGER_TT within the procedure.

  For an inline SQL splitter, you might also try something like this (requires 9i):

  DECLARE
  	p_csvlist VARCHAR2(100) := '2002, 7369, 7499, 7902, 7934';
  	v_count INTEGER;
  BEGIN
  	SELECT COUNT(*) INTO v_count
  	FROM   emp e
  	WHERE  e.empno IN
  	       ( SELECT EXTRACTVALUE(xt.column_value,'e')
  	         FROM   TABLE(XMLSEQUENCE
  	                ( EXTRACT
  	                  ( XMLTYPE('<coll><e>' || REPLACE(p_csvlist,',','</e><e>') || '</e></coll>')
  	                  , '/coll/*') )) xt );
  
  	DBMS_OUTPUT.PUT_LINE(v_count || ' rows');
  END;

  Or this (requires 10g):

  DECLARE
  	p_csvlist VARCHAR2(100) := '2002, 7369, 7499, 7902, 7934';
  	v_count INTEGER;
  BEGIN
  	SELECT COUNT(*) INTO v_count
  	FROM   emp e
  	WHERE  e.empno IN
  	       ( SELECT REGEXP_SUBSTR(p_csvlist, '[^,]+',1,ROWNUM)
  	         FROM   dual
  	         CONNECT BY ROWNUM <= LENGTH(p_csvlist) - LENGTH(REPLACE(p_csvlist,',')) );
  
  	DBMS_OUTPUT.PUT_LINE(v_count || ' rows');
  END;

  This is simpler, although it only works with numeric values:6

  DECLARE
  	p_csvlist VARCHAR2(100) := '2002, 7369, 7499, 7902, 7934';
  	v_count INTEGER;
  BEGIN
  	SELECT COUNT(*) INTO v_count
  	FROM   emp e
  	WHERE  e.empno IN
  	       ( SELECT TO_NUMBER(xt.column_value)
  	         FROM   XMLTABLE(p_csvlist) xt );
  
  	DBMS_OUTPUT.PUT_LINE(v_count || ' rows');
  END;

  Or there's even this:

  DECLARE
  	p_csvlist VARCHAR2(100) := '2002, 7369, 7499, 7902, 7934';
  	v_count INTEGER;
  BEGIN
  	WITH values_tab1 AS
  	       ( SELECT p_csvlist
  	              , LEVEL AS pos
  	              , SUBSTR(p_csvlist,ROWNUM,1) AS ch
  	              , COUNT(CASE WHEN SUBSTR(p_csvlist,ROWNUM,1) = ',' THEN '#' END)
  	                OVER (ORDER BY LEVEL) AS section
  	         FROM   dual CONNECT BY LEVEL <= LENGTH(p_csvlist) )
  
  	   , values_tab2 AS
  	       ( SELECT SUBSTR(p_csvlist,MIN(pos), 1 + MAX(pos) - MIN(pos)) AS val
  	         FROM   values_tab1
  	         WHERE  ch <> ','
  	         GROUP BY p_csvlist,section )
  
  	SELECT COUNT(*) INTO v_count
  	FROM   emp e
  	WHERE  e.empno IN
  	       ( SELECT val
  	         FROM   values_tab2 );
  
  	DBMS_OUTPUT.PUT_LINE(v_count || ' rows');
  END;
  
  

More suggestions

• The opposite problem: How do I convert multiple rows into a comma-separated list
• More about collections.
• It is also worth mentioning the construction WHERE empno MEMBER OF p_empno_list, where p_empno_list is a collection, available from 10g onwards. For a demo of this, see binding in-lists in 10g on Adrian Billington's oracle-developer.net.
• Tom Kyte posted a summary of techniques for handling in-list parameters on his blog: Varying IN-lists
• Tim Hall's Oracle Base has another summary: Dynamic IN-Lists.

© William Robertson 2004, 2007 www.williamrobertson.net

SELECT ct.owner, ct.type_name, ct.elem_type_name, ct.length
FROM   all_coll_types ct
     , all_types ot
WHERE  ct.coll_type = 'TABLE'
AND    ot.type_name(+) = ct.elem_type_name
AND    ot.owner(+) = ct.elem_type_owner
AND    ot.type_name IS NULL
ORDER BY ct.owner, ct.type_name;