Re: maximum number of expressions in a list is 1000

From: Job Miller <jobmiller_at_yahoo.com>
Date: Wed, 2 Jan 2013 06:31:35 -0800 (PST)
Message-ID: <1357137095.81269.YahooMailNeo_at_web126102.mail.ne1.yahoo.com>

If you put all those rows into a GTT and did a join, you'd get the same row back from the join 3 times, instead of only once. You'd have to have a unique constraint on the GTT i believe.

---

 From: Niall Litchfield <niall.litchfield_at_gmail.com> To: Timo Raitalaakso <rafu_at_iki.fi>
Cc: ORACLE-L <oracle-l_at_freelists.org> Sent: Wednesday, January 2, 2013 7:13 AM Subject: Re: maximum number of expressions in a list is 1000  

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?id01
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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


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

Received on Wed Jan 02 2013 - 15:31:35 CET