I am developing a report in Codeigniter, however my select contains a filter that can have more than 1000 expressions in my where_in and then it returns me the error:
ORA-01795: o número máximo de expressões em uma lista é de 1000.
I'm doing my whereabouts like this:
$this->db->where_in("R.CODIGO",$this->db->escape($filters['CODIGO']));
Where the variable $filters['CODIGO'] is an array with 1000 elements or more.
How to solve this?
Does your user enter with so many records like this? If so, the in is not perfornatico for such a scenario, alias, it does not even work. For this registration number I advise the use of a EXISTS
The exists works with a subquery, below an example of syntax:
SELECT *
FROM customers
WHERE EXISTS (SELECT *
FROM order_details
WHERE customers.customer_id = order_details.customer_id);
Chaos does not work you can make an adaptation and concatenate the in into several or limiting to thousand each, remembering that it will be slow and not correct, something close to that:
where
shogen_no in ('one', 'two', 'three') or
shogen_no in ('four', 'five', 'six') or ...