Re: SQL - long list in the where clause. Any ideas?

Don,

  You can try this. If you are using pl/sql program then use DBMS_UTILITY.COMMA_TO_TABLE. declare

   l_list   varchar2(1000) := '1,2,3,4,5,6,7,8,9,10';
   l_tablen binary_integer;
   l_tab    dbms_utility.uncl_array;

    dbms_utility.comma_to_table(l_list, l_tablen, l_tab);

    for i in 1 .. l_tablen
    loop

        YOUR SELECT STATEMENT ....
    end loop;
end;
/

KVNC DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E3FE80A.C47968EE_at_exesolutions.com>...
> Dale Edgar wrote:
>
> > On 31 Jan 2003 13:06:59 -0600, anon_at_anon.com (dev) wrote:
> >
> > >I now have a requirement that may cause this list to be huge. Oracle
> > >has a limitation of 2000 items in an IN statement. I have tried using
> > >many OR statements but perfomrance is terrible.
> >
> > Just as an aside, the number is actually considerably less (approx
> > 256) on early versions of Oracle 8 and all of Oracle 7.
> >
> > -Dale
> > -------------
> > DataBee: Create referentially correct small versions of large Oracle
> > databases for development and test. http://www.DataBee.com
>
> Well yes and no. Consider a bit of creativity such as UNION ALL. Or a
> mixture of BETWEEN and IN.
>
> Daniel Morgan
Received on Fri Feb 14 2003 - 12:28:09 CST