0

I generate query like this:

Dim goods As List = New List()
Dim rateValue as Integer = 100000
For i As Integer = 0 To rate
  goods.Add(i)
Next


Dim Sql As System.Text.StringBuilder = New System.Text.StringBuilder()
Sql.AppendLine("SELECT G.ID, G.NAME")
Sql.AppendLine("FROM GOODS G")
Sql.AppendFormat("WHERE {0}", GetSqlListPartition(goods,"G.ID"))

string GetSqlIDListPartition(List list, string propertyName) - Method partition List to condition like: (propertyName IN (...) OR propertyName IN (...) OR propertyName IN (...) ) in case List contains more 1000 elements

When I execute this query the error is appeared: ORA-00913: too many values.

But if I set rateValue = 10000 and generate query, it executes ok. What is the problem here?

I attached the query http://dfiles.ru/files/z1yq9iazj (i cant write it here because have error: "too long by 589752 characters")). The most interesting that if delete "0" from first IN it will executed ok.

ps. oracle version 10

user1881712
  • 99
  • 1
  • 1
  • 5
  • Can't you put the `goods` list into a temporary table and join it instead? – adrianm Sep 22 '13 at 16:28
  • Yes, i saw this solution in http://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause But in this case it will be more loading for data base (because fill and clear temp table every time) – user1881712 Sep 22 '13 at 16:32
  • Oracle can only handle up to 1000 (literal) values in an `IN` condition. –  Sep 22 '13 at 16:38
  • Databases are optimized for tables and set based operations like JOIN. An IN clause will be slower unless the number of values is small. I ran a test once on SQL-server where 200 values were faster in temp table than IN clause. Don't know where the limit is in your case but it must be below 100000. – adrianm Sep 22 '13 at 16:45
  • 2
    You're clearly generating an enormous query which is exceeding Oracle's limits. I see that an alternative approach has been suggested which you have dismissed. Get real. You need to take a different approach. Either explain clearly what you're trying to achieve, or expect the down votes to flow, and the question to be closed. –  Sep 22 '13 at 16:45
  • Ok, I try to explain what i need. – user1881712 Sep 22 '13 at 16:58
  • Ok, I try to explain what i need. In our program there are a lot of situation when i need to get additional information about knowing ids. So i have list of ids, which may be very long (for example 100000). Firstly we have way which use temp tables and every time put this List into temp table and further use temp table with join. But our DB administrator said that such way is additional loading for database (because a lot of clearing and inserting in temp table). So we decided to use partition list for IN – user1881712 Sep 22 '13 at 17:04
  • So, have you some advise how to prevent this problem? – user1881712 Sep 22 '13 at 18:37
  • 2
    Tell your DBA that temporary tables are created and cleared all the time by the database itself as part of normal query execution. Should you avoid ORDER BY as well? – adrianm Sep 23 '13 at 09:39
  • Getting same issue, basically it breaks Oracle so bad it seems the error message is misleading :) Theoretically it is a valid query. – Ben George Aug 14 '15 at 10:49

2 Answers2

-1

Is there only one propertyName? why not add and sequence in one IN condition, i.e. propertyName IN ('a', 'b', 'c'...)

Read through the description of the 00913 message here. http://www.techonthenet.com/oracle/errors/ora00913.php

Filip
  • 656
  • 4
  • 8
  • "why not add and sequence in one IN condition" try to add in one IN more than 1000 elements. It will error "ORA-01795" – user1881712 Sep 22 '13 at 16:21
  • I'm not that fast in reading VB code but it must be a limit in Oracle for the number of Where conditions. Did it work with 10,000 but not with 1,000 ORs? I dont get that – Filip Sep 22 '13 at 16:26
  • good hypothesis. I try to find in google information about limit of condition in where clause. – user1881712 Sep 22 '13 at 16:35
  • So i didn't find any information about limits of number in where condition – user1881712 Sep 23 '13 at 06:53
  • as been said here already, Oracle can only handle 1000 values in an IN condition. Try saving the Ids to a temporary table and perform a join against that table. Please give me some credits, this should be a simple probelm – Filip Sep 23 '13 at 07:01
  • Filip, hi. I attached text document with my query in http://dfiles.ru/files/z1yq9iazj – user1881712 Sep 25 '13 at 05:38
  • And i found strange thing - if delete "0" from the first IN statement the query will be executed successful. – user1881712 Sep 25 '13 at 05:39
-1

I'd guess you've exceeded Oracle's limit on the number of entries you can have in an IN clause. You need to reduce the number of entries - as you have found out by setting the list to 10,000 entries.

But, since your list starts at 0, and is made up of consecutive numbers, why not use 

SELECT ... WHERE G.ID <= 100000