How to solve the ORA-01795 (maximum number of expressions) error in Codeigniter

Question

I am developing a report in Codeigniter, but 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 Where 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?

Answer 1

Does your user enter so many records? If in the case is not perfornatico for such scenario, alias, it doesn’t even work. For this registration number I advise the use of a EXISTS

The exists works with a subquery, below an example 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 in several or limiting the thousand each, remembering that it will be slow and is not the correct, something close to this:

where
shogen_no in ('one', 'two', 'three') or
shogen_no in ('four', 'five', 'six') or ...