Did you know? Programmers convert coffee to code.

If you like my articles, sponsor me a coffee.

Handling ORA-01795 within a Spring application

OK, imagine you work on a software which has a lot of modules, you have a database in the background and you use it. You create, read, update and delete data, the reading queries contain sometimes an IN clause where you enter some values. Beside this you are damned you are forced you have to use Oracle as DBMS.

One day you get a bug report from your QA that something does not work what worked in the last… months. You look up the StackTrace (it’s good if you have one) and see following error message:

ORA-01795: maximum number of expressions in a list is 1000

Great. You have to solve this in a hurry because the guys and/or girls at the QA do not have anything better to do than ask you when will the error fixed. So you ask your best friend to help you out. In the first three results you’ll find the description of the error (see above, I copied it from there), an OTN discussion which you give up reading after a short period and an article from Burleson Consulting.

I admit that most of the time I like the answers of Burleson Consulting because they provide most of the time valuable informations and examples not just advices. However the tip for this problem is a bit disappointing: you have to use a subquery. Not always manageable — so you see that there is a link to StackOverflow (and you like that site too) you read the answer to the question: it says:

this is a oracle limitation in the number of list pass in the query.

  1. you will have to chop your query or
  2. provide a subquery/join in the IN clause instead.

So if they say to use a subquery too, let’s see how can it help. You take your query and make it look like

SELECT *
  FROM your_table
    WHERE foreign_id IN (first_thousand_elements)
    OR foreign_id IN (second_thousand_elements)
    OR ...

As a second approach you can write an inner select for your IN filtering:

SELECT *
  FROM your_table
    WHERE foreign_id IN (
       -- you provide here (if you can) the query which gives you the too long list
    )

As you can see this is not a great solution. But wait: there was a second option too: chop your query.
You can leave your query as it is but have to cut down the amount of parameters passed to it — but how.

Eventually you could write a utility method which takes a list and cuts it into pieces of a given size and than iterate over the result list of lists and execute your query for each sublist and merge the results together.

An example for the list splitter:

    public static <T> List<List<T>> splitLongList(final List<T> source, final int size)
    {
        if(source == null) return null; // you could use org.apache.commons.lang.Validate.notNull(source);

        int necessaryLists = (int) Math.ceil(source.size() / (float) size);
        List<T> sList = new ArrayList<T>(necessaryLists);

        if (size == 0 || source.size() <= size)
        {
            sList.add(source);
            return sList;
        }

        for (int i = 0; i < necessaryLists; i++)
        {
            if (i != (necessaryLists - 1))
                sList.add(source.subList(i * size, (i + 1) * size));
            else
                sList.add(source.subList(i * size, source.size()));
        }

        return sList;
    }

This is a usable solution if you have only one spot to deal with too long lists. If you work on a bigger project this is rather not the case so you’d have to repeat the code but this is against the DRY principle.

At work we use Spring so I thought about creating a wider and more transparent solution to this problem. I tried to use AOP and I solved my riddle with it.

There are many solutions with AOP, I tried 2 and I show here the one which is easy to configure.

First of all I created an annotation to use it for advising the methods having a potential big list as parameter.

@Target(METHOD)
@Retention(RUNTIME)
@Documented
public @interface BigListAdvice
{
    // intentionally left blank
}

Than I created an aspect to deal with the advised methods. The approach can be very different for various projects so I’ll show my version of the implementation.
There are some aspects usable for different UseCases. You can read about them more at the Spring Project WebSite. I’ve chosen the aroud advice because it is powerful by getting invoked with a method call — so at this point I can interfere and cut the long list into pieces, invoke the method and merge the results of each invocation together.

    public Object aroundMethod(ProceedingJoinPoint joinPoint) throws Throwable
    {
        Object[] parameters = joinPoint.getArgs();

        for (int i = 0; i < parameters.length; i++)
        {
            if (!(parameters[i] instanceof Collection))
            {
                continue;
            }

            List source = new ArrayList((Collection) parameters[i]);

            if (isNoBigList(source))
            {
                continue;
            }

            List<List<?>> listOfLists = CollectionUtils.splitLongList(source, MAX_LIST_SIZE);

            return callMethodForSmallLists(joinPoint, parameters, i, listOfLists, source.size());
        }

        return joinPoint.proceed();
    }

joinPoint.getArgs() lists all arguments passed to the method which is advised by the invoked around method.

Line 14 calls a method which looks if the list found among the arguments is a big list (with more elements than 1000) or not. If the list is OK we’ll jump to the next parameter.

In line 19 I pass a constant to the utility method. This is configured to 1000 and is used in the isNoBigList method too.

Line 21 executes the advised method with the small (splitted) lists and returns the result. You have probably noticed this works only for the first argument with a big list. Eventually you have to advise other methods in the call hierarchy to handle more than one big lists.

Line 24 is only invoked if there was no big list among the parameters.

I do not want to past all the code here, but if you have a question please feel free to ask me or if you’d like to see the whole code as one write a comment (and in the meantime I’ll try to upload the files to gitHub).
However there is one thing you have to take care of: if your method which you call within the advice returns a list you have to collect the result and return a list; if it returns boolean you have to evaluate all calls and return the AND of them all; if your method is a void you do not have to return anything. Below you find the code for void methods, other work similar.

    boolean isVoidMethod(ProceedingJoinPoint joinPoint)
    {
        boolean voidMethod = false;

        if (joinPoint.getSignature() instanceof MethodSignature)
        {
            MethodSignature signature = (MethodSignature) joinPoint.getSignature();
            Class<?> returnType = signature.getReturnType();
            voidMethod = returnType.equals(void.class);
        }

        return voidMethod;
    }

At the end I weaved all this together in a Spring configuration to get them work.

<aop:aspectj-autoproxy />

<bean id="bigListMethodAdvice"
    class="biz.hahamo.dev.util.persistence.services.BigListMethodAdvice"
    autowire="constructor" />

<aop:config>
    <aop:aspect ref="bigListMethodAdvice">
        <aop:pointcut id="advisedMethods" expression="@annotation(biz.hahamo.dev.util.persistence.annotations.BigListAdvice)"/>
        <aop:around pointcut-ref="advisedMethods" method="aroundMethod"/>
    </aop:aspect>
</aop:config>

And from this point we only have to annotate the methods across the whole project without any other configuration.

Notes:

  •  the advised method has to be called from another class: the advice does not work if you call the method from the same class (for example you have to call otherClass.advisedMethod where the method has the advising annotation)
  • if you use interfaces and implementations you have to annotate the implementation methods not the interface definitions (or at least I was not able to configure this)
GHajba
 

Senior developer, consultant, author, mentor, apprentice.

I love to share my knowledge and insights what I achieve through my daily work which is not trivial — at least not for me.

Click Here to Leave a Comment Below 2 comments
%d bloggers like this: