ORA-01795:列表中的最大表达式数为1000，如何拆分字符串 [英] ORA-01795: maximum number of expressions in a list is 1000 , how to split the string

问题描述

如何分割以下字符串?

      A.x IN (changeList),

changeList具有以下值，以使子句如下所示:

changeList has the following value, so that it makes the clause like this:

       A.x IN (HEXTORAW('1E2956B9266F11DDA85810000000C959'),HEXTORAW   
             ('ADD834AD6A3911DF923C10000000C959'),HEX...........

上面的IN有1000多个值，因此出现ORA-01795错误，如何对其进行修改，使我拥有

The above IN has more than 1000 values and hence ORA-01795 error, how to modify it so that i have

A.X IN(id1，..id999)或A.x IN(id1000，...)，任何C代码都可以帮助我...

A.X IN (id1, ..id999) OR A.x IN (id1000,...), Any c code would help me...

推荐答案

您必须编写一个循环，以每个有1000个条目的块形式执行SQL.

You must write a loop that executes the SQL in chunks with 1000 entries each.

或者，您可以将值插入表中，然后使用IN子句进行子选择，因为对于子选择，1000个条目的限制不适用.仅当您使用带有硬编码值字符串的SQL时，此限制.

Alternatively you can insert the values into a table and then do a subselect with the IN clause, because with a subselect, the limitation of 1000 entries doesn't apply. This limit is only when you use an SQL with an hardcoded value string.

类似这样的东西:

select *
from mytable t
where t.column1 = value
and t.column2 in
(
    select my_values
    from my_temp_table
)

这篇关于ORA-01795:列表中的最大表达式数为1000，如何拆分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！