How to solve Oracle IN clause limitation in Java ?

By arunraj

To solve Oracle IN clause limitation, refer to the article ORA-01795 Oracle In Clause Limitation.

There is one more work around to the issue, which can be solved, by splitting the huge query containing > 1000 values in IN clause in to smaller queries and joining them using UNION.
Have a look at the sample implementation for the same.

import java.util.ArrayList;
import java.util.List;

public class QueryUtil
{

        public static void main(String[] args)
        {
                QueryUtil qUtil = new QueryUtil();
                List<String> itemList = new ArrayList<String>();
                for (int i = 0; i < 1000; i++)
                {
                        itemList.add(new String("ACCESS_NETFRONTVER32_2010"));
                }

                String query = "SELECT * FROM DEVICES WHERE STATUS = 'AVAILABLE' ";
                System.out.println(qUtil.getInClauseQuery(itemList, query, "DEVICE_ID"));
        }

        public String getInClauseQuery(List<String> itemList, String query, String inClauseColumnName)
        {
                boolean tempFlag = false;
                int unionCount = 0;
                StringBuffer finalQuery = new StringBuffer();
                StringBuffer subQuery = new StringBuffer();
                String[] queryArr = query.split("WHERE");
                if (queryArr.length > 1)
                {
                        subQuery.append(" AND " + inClauseColumnName + " IN ( ");
                }
                else
                {
                        subQuery.append(" WHERE " + inClauseColumnName + " IN ( ");
                }
                if (itemList.size() > 999)
                {
                        for (int i = 0; i < itemList.size(); i += 500)
                        {
                                if (tempFlag)
                                {
                                        finalQuery.append(" UNION ");
                                        unionCount++;
                                }
                                StringBuffer sbTmp = new StringBuffer(1000);
                                boolean appendCom = false;

                                for (int j = i; (j < (i + 100)) && (j < itemList.size()); j++)
                                {
                                        if (appendCom)
                                        {
                                                sbTmp.append(",");
                                        }
                                        sbTmp.append("'").append(itemList.get(j)).append("'");
                                        appendCom = true;

                                }

                                finalQuery.append(query + subQuery + sbTmp.toString() + " ) \n");
                                tempFlag = true;
                        }

                }

                return finalQuery.toString();
        }
}

Output:

SELECT * FROM DEVICES WHERE STATUS = 'AVAILABLE'  AND DEVICE_ID IN ( 'ACCESS_NETFRONTVER32_201','ACCESS_NETFRONTVER32_202','ACCESS_NETFRONTVER32_203','ACCESS_NETFRONTVER32_204','ACCESS_NETFRONTVER32_205','ACCESS_NETFRONTVER32_206','ACCESS_NETFRONTVER32_207','ACCESS_NETFRONTVER32_208','ACCESS_NETFRONTVER32_209','ACCESS_NETFRONTVER32_210','ACCESS_NETFRONTVER32_211','ACCESS_NETFRONTVER32_212','ACCESS_NETFRONTVER32_213','ACCESS_NETFRONTVER32_214','ACCESS_NETFRONTVER32_215','ACCESS_NETFRONTVER32_216','ACCESS_NETFRONTVER32_217','ACCESS_NETFRONTVER32_218','ACCESS_NETFRONTVER32_219','ACCESS_NETFRONTVER32_220','ACCESS_NETFRONTVER32_221','ACCESS_NETFRONTVER32_222','ACCESS_NETFRONTVER32_223','ACCESS_NETFRONTVER32_224','ACCESS_NETFRONTVER32_225','ACCESS_NETFRONTVER32_226','ACCESS_NETFRONTVER32_227','ACCESS_NETFRONTVER32_228','ACCESS_NETFRONTVER32_229','ACCESS_NETFRONTVER32_230','ACCESS_NETFRONTVER32_231','ACCESS_NETFRONTVER32_232','ACCESS_NETFRONTVER32_233','ACCESS_NETFRONTVER32_234','ACCESS_NETFRONTVER32_235','ACCESS_NETFRONTVER32_236','ACCESS_NETFRONTVER32_237','ACCESS_NETFRONTVER32_238','ACCESS_NETFRONTVER32_239','ACCESS_NETFRONTVER32_240','ACCESS_NETFRONTVER32_241','ACCESS_NETFRONTVER32_242','ACCESS_NETFRONTVER32_243','ACCESS_NETFRONTVER32_244','ACCESS_NETFRONTVER32_245','ACCESS_NETFRONTVER32_246','ACCESS_NETFRONTVER32_247','ACCESS_NETFRONTVER32_248','ACCESS_NETFRONTVER32_249','ACCESS_NETFRONTVER32_250','ACCESS_NETFRONTVER32_251','ACCESS_NETFRONTVER32_252','ACCESS_NETFRONTVER32_253','ACCESS_NETFRONTVER32_254','ACCESS_NETFRONTVER32_255','ACCESS_NETFRONTVER32_256','ACCESS_NETFRONTVER32_257','ACCESS_NETFRONTVER32_258','ACCESS_NETFRONTVER32_259','ACCESS_NETFRONTVER32_260','ACCESS_NETFRONTVER32_261','ACCESS_NETFRONTVER32_262','ACCESS_NETFRONTVER32_263','ACCESS_NETFRONTVER32_264','ACCESS_NETFRONTVER32_265','ACCESS_NETFRONTVER32_266','ACCESS_NETFRONTVER32_267','ACCESS_NETFRONTVER32_268','ACCESS_NETFRONTVER32_269','ACCESS_NETFRONTVER32_270','ACCESS_NETFRONTVER32_271','ACCESS_NETFRONTVER32_272','ACCESS_NETFRONTVER32_273','ACCESS_NETFRONTVER32_274','ACCESS_NETFRONTVER32_275','ACCESS_NETFRONTVER32_276','ACCESS_NETFRONTVER32_277','ACCESS_NETFRONTVER32_278','ACCESS_NETFRONTVER32_279','ACCESS_NETFRONTVER32_280','ACCESS_NETFRONTVER32_281','ACCESS_NETFRONTVER32_282','ACCESS_NETFRONTVER32_283','ACCESS_NETFRONTVER32_284','ACCESS_NETFRONTVER32_285','ACCESS_NETFRONTVER32_286','ACCESS_NETFRONTVER32_287','ACCESS_NETFRONTVER32_288','ACCESS_NETFRONTVER32_289','ACCESS_NETFRONTVER32_290','ACCESS_NETFRONTVER32_291','ACCESS_NETFRONTVER32_292','ACCESS_NETFRONTVER32_293','ACCESS_NETFRONTVER32_294','ACCESS_NETFRONTVER32_295','ACCESS_NETFRONTVER32_296','ACCESS_NETFRONTVER32_297','ACCESS_NETFRONTVER32_298','ACCESS_NETFRONTVER32_299','ACCESS_NETFRONTVER32_300' )
 UNION SELECT * FROM DEVICES WHERE STATUS = 'AVAILABLE'  AND DEVICE_ID IN ( 'ACCESS_NETFRONTVER32_701','ACCESS_NETFRONTVER32_702','ACCESS_NETFRONTVER32_703','ACCESS_NETFRONTVER32_704','ACCESS_NETFRONTVER32_705','ACCESS_NETFRONTVER32_706','ACCESS_NETFRONTVER32_707','ACCESS_NETFRONTVER32_708','ACCESS_NETFRONTVER32_709','ACCESS_NETFRONTVER32_710','ACCESS_NETFRONTVER32_711','ACCESS_NETFRONTVER32_712','ACCESS_NETFRONTVER32_713','ACCESS_NETFRONTVER32_714','ACCESS_NETFRONTVER32_715','ACCESS_NETFRONTVER32_716','ACCESS_NETFRONTVER32_717','ACCESS_NETFRONTVER32_718','ACCESS_NETFRONTVER32_719','ACCESS_NETFRONTVER32_720','ACCESS_NETFRONTVER32_721','ACCESS_NETFRONTVER32_722','ACCESS_NETFRONTVER32_723','ACCESS_NETFRONTVER32_724','ACCESS_NETFRONTVER32_725','ACCESS_NETFRONTVER32_726','ACCESS_NETFRONTVER32_727','ACCESS_NETFRONTVER32_728','ACCESS_NETFRONTVER32_729','ACCESS_NETFRONTVER32_730','ACCESS_NETFRONTVER32_731','ACCESS_NETFRONTVER32_732','ACCESS_NETFRONTVER32_733','ACCESS_NETFRONTVER32_734','ACCESS_NETFRONTVER32_735','ACCESS_NETFRONTVER32_736','ACCESS_NETFRONTVER32_737','ACCESS_NETFRONTVER32_738','ACCESS_NETFRONTVER32_739','ACCESS_NETFRONTVER32_740','ACCESS_NETFRONTVER32_741','ACCESS_NETFRONTVER32_742','ACCESS_NETFRONTVER32_743','ACCESS_NETFRONTVER32_744','ACCESS_NETFRONTVER32_745','ACCESS_NETFRONTVER32_746','ACCESS_NETFRONTVER32_747','ACCESS_NETFRONTVER32_748','ACCESS_NETFRONTVER32_749','ACCESS_NETFRONTVER32_750','ACCESS_NETFRONTVER32_751','ACCESS_NETFRONTVER32_752','ACCESS_NETFRONTVER32_753','ACCESS_NETFRONTVER32_754','ACCESS_NETFRONTVER32_755','ACCESS_NETFRONTVER32_756','ACCESS_NETFRONTVER32_757','ACCESS_NETFRONTVER32_758','ACCESS_NETFRONTVER32_759','ACCESS_NETFRONTVER32_760','ACCESS_NETFRONTVER32_761','ACCESS_NETFRONTVER32_762','ACCESS_NETFRONTVER32_763','ACCESS_NETFRONTVER32_764','ACCESS_NETFRONTVER32_765','ACCESS_NETFRONTVER32_766','ACCESS_NETFRONTVER32_767','ACCESS_NETFRONTVER32_768','ACCESS_NETFRONTVER32_769','ACCESS_NETFRONTVER32_770','ACCESS_NETFRONTVER32_771','ACCESS_NETFRONTVER32_772','ACCESS_NETFRONTVER32_773','ACCESS_NETFRONTVER32_774','ACCESS_NETFRONTVER32_775','ACCESS_NETFRONTVER32_776','ACCESS_NETFRONTVER32_777','ACCESS_NETFRONTVER32_778','ACCESS_NETFRONTVER32_779','ACCESS_NETFRONTVER32_780','ACCESS_NETFRONTVER32_781','ACCESS_NETFRONTVER32_782','ACCESS_NETFRONTVER32_783','ACCESS_NETFRONTVER32_784','ACCESS_NETFRONTVER32_785','ACCESS_NETFRONTVER32_786','ACCESS_NETFRONTVER32_787','ACCESS_NETFRONTVER32_788','ACCESS_NETFRONTVER32_789','ACCESS_NETFRONTVER32_790','ACCESS_NETFRONTVER32_791','ACCESS_NETFRONTVER32_792','ACCESS_NETFRONTVER32_793','ACCESS_NETFRONTVER32_794','ACCESS_NETFRONTVER32_795','ACCESS_NETFRONTVER32_796','ACCESS_NETFRONTVER32_797','ACCESS_NETFRONTVER32_798','ACCESS_NETFRONTVER32_799','ACCESS_NETFRONTVER32_800' )
  • 6606 reads

Search