oracle - ORA-01795: 列表中的最大表达式数为 1000 perl 脚本中的错误

Question

我正在尝试使用 NOT IN 子句运行查询，例如：

SELECT * FROM table WHERE column NOT IN (?,?,...) (>1000 items)我得到 ORA-01795: maximum number of expressions in a list is 1000 错误。

在我的脚本中，我正在做类似的事情：

my $lparam = join ', ' => ('?') x @ids; 
$lquery = "SELECT * FROM table WHERE column NOT IN ($lparam)";

$lcsr = $zdb->prepare($lquery);
$lcsr->execute( @ids );

我想将 NOT IN 子句拆分为where (A not in (a,b,c) AND A not in (d,e,f)) ...我们如何实现这一点？

标签： oracleperloracle11g

Answer 1

给你，添加三元组并计算它们。

my $count = 0;
$lquery = "SELECT * FROM table WHERE (A ";
while (@ids -$count > 3)  {
    $lquery .= "NOT in (?, ?, ?) AND A ";
    $count += 3;
}
my $lparam = join ', ' => ('?') x (@ids - $count); 

$lquery .= "NOT  IN ($lparam))";