Re: maximum number of expressions in a list is 1000

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Wed, 2 Jan 2013 12:13:14 +0000
Message-ID: <CABe10saeTsNiq__M5XBa_0LNH7Vb9-248xKr5xXdBfeAizE7+A_at_mail.gmail.com>

Nice link thanks Timo. I also like the pointer to jdbc batching in the comments to your update article.I don't understand your duplicates comment though. For example given table t1
create table t1(

     id number
, content varchar2(500));

insert into t1(id,content) values( 1, 'My Row'); commit;

The output of

select content from t1 where id in (1,1,1);

is the same single row as

select content from t1 where id in (1);

On Wed, Jan 2, 2013 at 11:29 AM, Timo Raitalaakso <rafu_at_iki.fi> wrote:

>
> If you are using Java here is more information on top of Adrian
> Billington's approach
> http://rafudb.blogspot.fi/2011/10/variable-inlist.html Describing
> ArrayDescriptor.createDescriptor to use the table of sql type as a bind
> from Java.
>
> I would prefer sticking with in list instead of changing the query to a
> join. It might be that someone is binding the same values several times
> and so your query result changes to include duplicates.
>
> --
> Timo Raitalaakso
>
> On 2.1.2013 12:40, Niall Litchfield wrote:
> > On Wed, Jan 2, 2013 at 10:22 AM, Norman Dunbar <oracle_at_dunbar-it.co.uk
> >wrote:
> >>> Is there a workaround to avoid this error in oracle?
> >>
> > or even try approaching that problem as
> >
> > select <the columns I really want in the end>
> > from t1 JOIN t2
> > where FILTER
> >
> > replacing JOIN and FILTER as appropriate.
> >
> > ISTM that the most appropriate answer to the original question will
> depend
> > upon where your list of ID values actually comes from. You may well find
> > Adrian Billington's article on binding in lists useful as well.
> > http://www.oracle-developer.net/display.php?id=301
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 02 2013 - 13:13:14 CET