How to fix ORA-01795:max number of expressions in a list is 1000 error when updating rows using Spring Integration's jdbc inbound channel adapter

I have written adapter below to poll messages from DB. This adapter polls rows from table where type = 'ABC' and then updates status to 'P'.

I am polling over 1000 records, which throws ORA-01795: maximum number of expressions in a list is 1000 error

How can I handle this in Spring Integrations jdbc inbound channel adapter.

<int-jdbc:inbound-channel-adapter query="select id, type, status, location from products where type='ABC'"
    channel="target" data-source="dataSource"
    update="update item set status='P' where id in (:id)">
   <int-pollar fix-delay="60" time-units="SECONDS"/>
</int-jdbc:inbound-channel-adapter/>

Solution

you can split your query into multiple smaller queries that each return a maximum of 1000 rows. You can use the WHERE id BETWEEN x AND y clause to specify the range of ids to retrieve for each query:

<select id="selectProducts" resultType="Product">
  select id, type, status, location from products where type='ABC' and id 
  between #{startId} and #{endId}
</select>

in Java code, you can then loop through the range of ids and execute the query multiple times, each time specifying a different range of ids:

int batchSize = 1000;
for (int i = 0; i < totalCount; i += batchSize) {
  List<Product> products = mybatis.selectList("selectProducts", 
  Map.of("startId", i, "endId", i + batchSize));
  // process the products
}

alternatively: Spring Integration provides a built-in solution to handle the scenario you described, which is to use the max-rows-per-poll property in the int-jdbc:inbound-channel-adapter configuration.

This property limits the maximum number of rows that will be fetched in each polling cycle, preventing the ORA-01795 error from occurring. For example, you can set max-rows-per-poll="1000" to limit the number of rows fetched per poll to 1000:

<int-jdbc:inbound-channel-adapter query="select id, type, status, 
    location from products where type='ABC'"
    channel="target" data-source="dataSource"
    update="update item set status='P' where id in (:id)"
    max-rows-per-poll="1000">
   <int-pollar fix-delay="60" time-units="SECONDS"/>
</int-jdbc:inbound-channel-adapter>

With this configuration, the int-jdbc:inbound-channel-adapter will fetch at most 1000 rows in each polling cycle, and will continue polling until all rows that match the query have been fetched and processed.