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 When SQL is expecting a character string such as SMITH, but is passed a comma-separated list such as SMITH,JONES,FORD,MILLER, no error is produced, but the query simply returns no rows. 

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 Until 12c, the only types of collection that SQL can handle are those you define in SQL - types defined in a PL/SQL package are not recognised (PL/SQL understands SQL, not the other way around). This gives you a choice between Nested Table types and VARRAYs. I recommend using Nested Table types and not VARRAYs unless there is some good reason to set a limit to the number of elements (which VARRAYs let you do), for example if the collection is designed to contain some particular number of elements (twelve months, seven days, five sexes etc).

You create a scalar collection type3 You can check the scalar collection types already available by querying all_coll_types. 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
        
        In 9i, SELECT * used with TABLE() and no explicit CAST()
        gave error ORA-22905 cannot access rows from a non-nested table item.
        
        from   emp
        where  empno in
               ( select column_value5COLUMN_VALUE is the default attribute name in a scalar collection type.
                 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:

More suggestions