ORA-01795: perl 脚本中列表中的最大表达式数为 1000 错误 [英] ORA-01795: maximum number of expressions in a list is 1000 error in perl script

问题描述

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

I'm trying to run a query with NOT IN clause like:

SELECT * FROM table WHERE column NOT IN (?,?,...) (>1000 items) 并且我得到 ORA-01795:列表中的最大表达式数是1000 错误.

SELECT * FROM table WHERE column NOT IN (?,?,...) (>1000 items) and I'm getting ORA-01795: maximum number of expressions in a list is 1000 error.

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

In my script I'm doing something like:

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)) ... 怎样才能我们实现了这一目标?

I want to split the NOT IN clause to something like where (A not in (a,b,c) AND A not in (d,e,f)) ... How can we achieve this?

推荐答案

开始吧，添加三元组并计算它们.

Here you go, adding triples and counting them.

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))";

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