I do not like this kind of dynamic NOT IN clauses. It is better to have a temporary table, a bit like in
create global temporary table t(x number not null);
insert into t(x) values (:b1);
insert into t(x) values (:b2);
...
insert into t(x) values (:b9999);
select foo from bar where c not in (select x from t);
If you want to however do this in one query you can still use AND
SQL> select foo from bar where c not in (
:b1,
:b2,
...
:b9999);
*
ERROR at line 1002:
ORA-01795: maximum number of expressions in a list is 1000
SQL> select foo from bar where
c!=:b1 and
c!=:b2 and
...
c!=:b9999;
FOO
---
foo
Translate c NOT IN (exprlist) into c!=expr1 and c!=expr2…
Translate c IN (exprlist) into c=expr1 or c=expr2…
If you use dynamic expression list, this will bypass the ORA-01795 error