Limitation of IN Operator in Oracle 9i

There is no direct limitation on the number of values in this list.
You can use IN with sub-select statement that may return any number of values:
IN (SELECT emp_no from employees)
The only real restriction is the max length of SQL statement - 4GB.

i get this error when using a query from an asp page ..

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC driver for Oracle][Oracle]ORA-01795: maximum number of expressions in a list is 1000

and part of my query looks like this..

sqlWHERE = "synoRoadmap.prty_code in " & Chaine_ListeComboProdType
sqlWHERE = sqlWHERE & " AND synoRoadmap.prod_code in " & Chaine_ListeProdCode(here i get more than 1000 products)
sqlWHERE = sqlWHERE & " AND synoRoadmap.regi_code = '" & Chaine_RegiCode &"'"
sqlWHERE = sqlWHERE & " AND synoRoadmap.regi_code = region.regi_code " 

what do you think about the error OMC2000?

Hi,

There is a work-around for this problem. I dont know how far this solution wld help u. here it is ...


I CREATED A TABLE NZTB1

create table nztb1 ( col1 number(10));

INSERTED 1000 RECORDS INTO IT.

declare
      n number(10);
begin
      for n in 1..1000
  loop
   insert into nztb1 values (n);
  end loop;
  commit;
end;  


CHECKED THE 1000 ROW COUNT

select count(1) from nztb1;


NOW IF I USE A NORMAL IN CLAUSE WITH MORE THAN 1000 ELEMENTS, IT GIVES THE SAME ERROR AS WHAT U GET ... NOTE AFTER THE LOOP I HAVE ADDED 1 MORE ELEMENT SO THAT THE ELEMENTS IN THE IN CLAUSE BECOME 1001

declare
 sqlstmt varchar2(13000);
 ctr number(10);
begin
      sqlstmt := '';
      for n in 1..1000
  loop
   sqlstmt := sqlstmt || n || ', ';
  end loop;      
  sqlstmt := sqlstmt || ' 0';
  sqlstmt := 'select count(1) from nztb1 where col1 in ( ' || sqlstmt || ' )';
  execute immediate sqlstmt into ctr;
  dbms_output.put_line(ctr);
end;  


NOW FOR THE WORK-AROUND. I DONT KNOW HOW FAR THIS WOULD WORK FOR U .. FOR ME IT WORKED BECAUSE THE LENGHT SQLSTMT IS LESS THAN 32767 CHARACTERS..

WHAT I AM DOING IS I PREPARED A SELECT INSIDE THE IN CLAUSE WITH UNION OF ALL VALUES ... FOR ME IT WORKED FINE. BUT I WLD ADVISE TO CHECK THE SPEED ISSUE OF THIS PROCEDURE ALSO BECUASE I AM USING AN UNION

declare
 sqlstmt varchar2(32767);
 ctr number(10);
begin
      sqlstmt := '';
      for n in 1..1000
  loop
   sqlstmt := sqlstmt || ' select ' || n || ' from dual ' || ' UNION ';
  end loop;      
  sqlstmt := sqlstmt || ' select 1 from dual ';
  sqlstmt := 'select count(1) from nztb1 where col1 in ( ' || sqlstmt || ' )';
  execute immediate sqlstmt into ctr;
 dbms_output.put_line(ctr);
end;  

Let me know the test resuilts

Rgds,
Nazim M

the problem was solved long ago but was posting this now..we changed the sql so instead of expression we used subquery from temporary table..thanks OMC2000