Re: is there limit on "in operator"?

Its 1000 elements.
I ran a test
customer_at_HL163DV1> ed
Wrote file afiedt.buf

  1 declare
  2 v_sql varchar2(32767) := 'select * from dual where 1 in (';   3 begin
  4 for i in 1..1000 loop
  5 v_sql := v_sql || i || ',';
  6 end loop;
  7 v_sql := v_sql || '1001' || ')';
  8 execute immediate v_sql;
  9* end;
customer_at_HL163DV1> /
declare
*
ERROR at line 1:
ORA-01795: maximum number of expressions in a list is 1000 ORA-06512: at line 8

customer_at_HL163DV1> ed
Wrote file afiedt.buf

  1 declare
  2 v_sql varchar2(32767) := 'select * from dual where 1 in (';   3 begin
  4 for i in 1..999 loop
  5 v_sql := v_sql || i || ',';
  6 end loop;
  7 v_sql := v_sql || '1001' || ')';
  8 execute immediate v_sql;
  9* end;
customer_at_HL163DV1> /

PL/SQL procedure successfully completed.

Fred Pierce <fpierce_at_avialantic.com> wrote in message news:<3DAEBC9F.1080607_at_avialantic.com>...
> Joe D wrote:
>
> > I have to use a select query using in operator where the in clause
> > can not be a subquery since the conditions are not in the database.
> > The query like:
> > select * from mytable where mycolum in
> > (very-long-concatenated-string-for-values);
> >
> > I am concerned that if there is limit on in, then if currently i do
> > not run into trouble, sometime maybe.
> >
> > Can anyone help? Thanks
> >
>
> I thought I found a limit back in 7.1.x but the topic came up recently
> and I couldn't find any indication of a limit in any documentation thus
> far. Tried a few tests with very large strings and lots of elements and
> was not able to find a limit. Not very scientific I admit - didn't note
> how many elements or lengths of strings - was only interested in what we
> were likely to exceed on our project.
>
> Am curious if this limit or lack thereof is documented somewhere.
>
> fdp
>
> --------------------------------------------------------------------
> Fred Pierce (DNRC)- fpierce_at_avialantic.com
> Mid-Atlantic Aviation on the Web - http://www.avialantic.com
> ** Mid Atlantic Air Museum WWII Weekend Airshow June 6-8 2003
> - www.maam.org/maamwwii.html **
> *** World Airshow News - www.worldairshownews.com **
> ---------------------------------------------------------------------
Received on Fri Nov 01 2002 - 19:04:47 CET