ORA-00913大量使用"IN"时出错陈述 [英] ORA-00913 Error when use a lot of "IN" statement

问题描述

我生成这样的查询:

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(列表列表，字符串propertyName)-方法分区列表，其条件如下:(propertyName IN(...)或propertyName IN(...)或propertyName IN(...))，以防List包含更多内容1000个元素

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

当我执行此查询时，出现错误:ORA-00913: too many values.

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

但是，如果我设置rateValue = 10000并生成查询，它将执行ok.这是什么问题?

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

我附加了查询 http://dfiles.ru/files/z1yq9iazj (我无法写它这是因为有错误:太长了589752个字符")). 最有趣的是，如果从第一个IN删除"0"，它将执行确定.

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版本10

ps. oracle version 10

推荐答案

我猜想您已经超出了Oracle在IN子句中可以拥有的条目数的限制.您需要减少条目的数量-通过将列表设置为10,000个条目可以发现.

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.

但是，由于您的列表从0开始并且由连续的数字组成，所以为什么不使用

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

SELECT ... WHERE G.ID <= 100000 

这篇关于ORA-00913大量使用"IN"时出错陈述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！