How many elements does the IN clause support

I had taken this quetions and reply from Best Practice PL/SQL
with Steven Feuerstein available at OTN.

How many elements does the IN clause support?
Asked by Alejandra Vergara from Somewhere in South America on Tuesday, July 27, 2004

Question:
I have a query of type select * from table1 where cod_table in ( 1,2,...,300). Does the IN clause in Oracle9i Database support more than 256 elements?

Answer:
Alejandra, in Oracle9i, you can have up to 1,000 elements in your IN list.

I take it you are worried about errors like this:

ORA-01795: maximum number of expressions in a list is 1000

And that is a valid concern....we might ask the same question about Oracle Database 10g and then the future releases as well. One way to get the answer is to check Oracle documentation, but an even better way is to check by running code.

I answered your question to my satisfaction by creating the following program:


CREATE OR REPLACE PROCEDURE test_in_limit (
table_in IN VARCHAR2
,int_column_in IN VARCHAR2
,limit_in IN PLS_INTEGER
)
IS
v_sql VARCHAR (32767);
i integer;
BEGIN
v_sql :=
'SELECT ' || int_column_in || ' FROM ' || table_in ||
' WHERE ' || int_column_in || ' IN (1';

FOR indx IN 2 .. limit_in
LOOP
v_sql := v_sql || ',' || indx;
END LOOP;

v_sql := v_sql || ')';

EXECUTE IMMEDIATE v_sql into i;
END test_in_limit;
/

(Source code available here.) I then ran it as follows without any error on Oracle9i:

BEGIN
test_in_limit ('sg_script', 'id', 1000);
END;
/

Yet here is what happened with 1,001:

SQL> BEGIN
2 test_in_limit ('sg_script', 'id', 1001);
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-01795: maximum number of expressions in a list is 1000
ORA-06512: at "QNXO_DEV.TEST_IN_LIMIT", line 24
ORA-06512: at line 2

Running this program on Oracle Database 10g has identical results.