Scalability and Oracle ‘IN’ operator

The following solution is specific if you are using iBatis. Though it is not very common to have more than 1000 elements in Oracle "IN" operator if the system and ORM mappings are designed correctly, we all know it is not a perfect world.
The default limitation of Oracle ‘IN’ clause is 1000 elements (‘1’,’2’,’-‘,’1000’) but wha happens if you have 1005 elements. There are 2 ways one is configuring Oracle if you happen to have right permissions and DBAs are nice to you. But if that is not the case you can fall back on 2nd option as shown below.

For example this is what you have in Ibatis XML
< select id="getProductGroup" resultmap="result" parameterclass="java.lang.String">
---1005 comma separated strings (elements)
select * from table where xxx in #value#
</select>


The above works fine as long as you have 1000 comma separated strings but if you have more than 1000, you can replace it with the following.

< select id=" getProductGroup " resultmap="result" parameterclass="java.util.List">
--List containing comma separated strings
select * from table where
(
(instr (#[]# , ','||xxx||',') > 0)
)

< /select>

Of course, you will have to write utility class to create the parameter List.

Passing params to Struts Mapping

We all come across situations where we have to pass params to Struts Action Mapping from Action class to create new ActionForward. As with most of my posts my intent is to learn and share.

Here is your Action Class

public class ResourceAction extends Action {


public ActionForward filter(ActionMapping mapping, ActionForm form, pServletRequest request,
HttpServletResponse response)

//other code here
//Original Forward

return mapping.findForward(ForwardConstants.SUCCESS);

//Create new Action Forward with additional params

ActionForward actionForward = mapping.findForward(ForwardConstants.SUCCESS);
ActionForward newActionForward = new ActionForward (actionForward);
newActionForward.setPath(actionForward.getPath()+ paramString); //create new Forward
return newActionForward;


}

}