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:1ERROR 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 (
'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
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:
VARCHAR2parameter. Within this second version, convert the
VARCHAR2string into an
INTEGER_TTcollection using a
SPLIT()function (you'll have to write your own - an example is here), and call the first version passing the
INTEGER_TTwithin 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;
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;
WHERE empno MEMBER OF p_empno_list, where
p_empno_listis a collection, available from 10g onwards. For a demo of this, see binding in-lists in 10g on Adrian Billington's oracle-developer.net.
© William Robertson 2004, 2007 www.williamrobertson.net
SMITH,JONES,FORD,MILLER, no error is produced, but the query simply returns no rows.
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;
SELECT *used with
TABLE()and no explicit
ORA-22905 cannot access rows from a non-nested table item.