Re: maximum number of expressions in a list is 1000
- From: Job Miller <jobmiller@xxxxxxxxx>
- To: "niall.litchfield@xxxxxxxxx" <niall.litchfield@xxxxxxxxx>
- Date: Wed, 2 Jan 2013 06:31:35 -0800 (PST)
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@xxxxxxxxx>
To: Timo Raitalaakso <rafu@xxxxxx>
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
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@xxxxxx> 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@xxxxxxxxxxxxxxx
> >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
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
