Re: maximum number of expressions in a list is 1000

From: Norman Dunbar <oracle_at_dunbar-it.co.uk>
Date: Wed, 02 Jan 2013 10:22:56 +0000
Message-ID: <50E40A80.6000808_at_dunbar-it.co.uk>



Morning,

On 02/01/13 10:00, Jose Soares wrote:

> SELECT id, name from anagrafica WHERE id in (100,32,43,66,2,1,334,
> ...... more than 1000 values)
> and I got this error:
>
> cx_Oracle.DatabaseError: ORA-01795: maximum number of expressions in a
> list is 1000

That's an Oracle hard limit unfortunately.

> Is there a workaround to avoid this error in oracle?

I had a similar problem a few years back. A Java application (hack, spit!) was running a select to fetch a list of IDs:

SELECT ID
FROM table_x
WHERE something;

It then built a new SQL statement with a '?' in it for each ID returned by the above. The new statement selected further information from the same table. (Go figure!)

It then BINDed each ID to each '?' in the new statement and executed that as a parameterised statement.

SELECT lots-of-stuff
FROM table_x
WHERE ID in (......);

It "worked fine" until the first query started to return more than 1000 IDs, then it hit the same problem.

To work around it, you simply have to replace the individual IDs with a query, as in:

SELECT id, name from anagrafica WHERE id in (

    SELECT id FROM ....
    WHERE something ....
)

That way, it matters not a jot if you have 1, 1000, 10,000 or millions of potential IDs in the sub-select, it will just work. You only hit the 1000 limit with hard coded values, whether BINDed or not.

Obviously, your sub-select needs to be able to identify the criteria that will return the desired IDs for the outer select.

There is another work around, as I implemented. My problem was that the Java was doing two separate SELECTs on the same table. The first to obtain a number of IDs, the second to retrieve further information, from the same table, as the originally chosen IDs.

I combined the two selects to simply retrieve the required data in a single statement.

SELECT lots-of-stuff
FROM table_x
WHERE something;

Java? Just say no! ;-)

Good luck.

Cheers,
Norm.

-- 
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 02 2013 - 11:22:56 CET

Original text of this message