Mule Oracle Database Connector SQL with IN OPERATOR

I've problems with database connector of mule, which i was using for select query. I have an arraylist of string to give inside in parameter.

Mule Sql Query - passing parameters to the IN operator

Solution mentioned above doesn't work with 3.7.3 Mule ESB, I've tried in many ways and searched for that. Except this document there is no definite way which i've founded till this time.

I am using query below :

select * from db_table where id in (2,3,4)

On my example 2,3,4 is inside my flow variable which contains arraylist.

Any suggestions ?

Solution

<set-variable variableName="CHUNKVIBLIST" value="#[new ArrayList()]" doc:name="Set CHUNKVIBLIST Variable"/>
    <expression-component doc:name="Expression"><![CDATA[int chunkSize = 1000;
            for (int i = 0; i < flowVars.VIBLIST.size(); i += chunkSize) {      flowVars.CHUNKVIBLIST.add("'"+org.apache.commons.lang.StringUtils.join(flowVars.VIBLIST.subList(i, i + chunkSize >=  flowVars.VIBLIST.size() ?  flowVars.VIBLIST.size() : i + chunkSize),"','")+"'");
            }]]></expression-component>
        
    <foreach collection="#[flowVars.CHUNKVIBLIST]" doc:name="For Each matnr batch:">
    <db:select config-ref="Mule_DB_Configuration" doc:name="Select statement">
            <db:dynamic-query><![CDATA[SELECT DISTINCT(MATNR) FROM cated_prodrelease where MATNR IN (#[payload])]]></db:dynamic-query>
    </db:select>
    </foreach>

Update on 01.03.2021 Old answer was my beginner days, i just decided to update my answer and changed with above, you should create payload contains comma seperated string to use that answer which is using IN statement as dynamic query.