Did you know? Programmers convert coffee to code.

If you like my articles, sponsor me a coffee.

At the other end of the link I post some results of my other hobbies, like playing bass. It is worth checking it out ;)

Handling ORA-01795 with Java EE

Some time ago I’ve written about how to handle the ORA-01795 error code within a Spring application. Now it is time to look at this error from a Java EE application.

Preparing the project

As I prepared this article I stumbled upon a minor problem: how to set up a Java EE 7 project?  Because it is not as easy to include it into this article I’ve created a separate article you can read here.

What you need to do is get an Oracle database instance and configure the example-ds.xml file giving it the connection to the Oracle DB. This is needed because ORA exceptions are coming only from Oracle databases.

An example-ds could look like this:

<datasources xmlns="http://www.jboss.org/ironjacamar/schema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://www.jboss.org/ironjacamar/schema http://docs.jboss.org/ironjacamar/schema/datasources_1_0.xsd">
   <!-- The datasource is bound into JNDI at this location. We reference this in META-INF/persistence.xml -->
   <datasource jndi-name="java:jboss/datasources/exampleDS" pool-name="example" enabled="true" use-java-context="true">

Preparing the test data

This will not be a big thing because all we need is a list containing 1001 elements and passing it to the repository / entity manager.

List<Long> idList = new ArrayList<>(1001);
for(long i = 0; i < 1001; i++) {

If you ask me, why did I set the initial capacity of the List to 1001, I’d tell you how ArrayLists work in Java:

In the background the elements of the list are stored in an array — which has to have a size. This size is fixed although ArrayList is flexible and let you add elements even if the array is full. For this it creates a new array with a new size and copies the elements into this new array. The size of the array is counted like:

newSize = (oldSize * 3)/2 +1

The initial size of the array is 10 — this will be set if you create an ArrayList with the no-arg constructor. In the example above, where we add 1001 elements to our list it would copy the array 11 times to end up with the capacity of 1019. So with setting the initial capacity to 1001 we save some CPU time  and garbage collection of shuffling our data.

The second thing we need is an entity and a table containing the entity. Because Hibernate is configured to auto-create the schema every time the server is started we do not need to worry about this. I’ve added the TestEntity entity to the project. It is really a simple entity with only the necessary data to have a table with a field created.

Causing the ORA-01795

This is easy too: we just have to call a repository method and providing an SQL statement with an “IN”-clause, and this “IN”-clause will be filled with the list containing the 1001 (fake) IDs. Easy, isn’t it?

public void createOra01795() {
    Query query = entityManager.createQuery("FROM Fehler WHERE id in (:ids)", String.class);
    query.setParameter("ids", generateBigList());

The generateBigList() method is the one from above which generates the list with too many elements for an IN-query.

If you call this method now you’ll get an exception similar to this one:

Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
    at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:56) [hibernate-core-4.2.0.SP1-redhat-1.jar:4.2.0.SP1-redhat-1]
    ... 54 more

The stack trace can vary because of the dependencies your server is using (Hibernate-core).

Handling the problem

Handling the problem is not easy. It never is. This is because everyone can get a hold of an EntityManager instance with

private EntityManager entityManager;

in any managed service in the application. And this is the biggest problem.

However if you can encourage the developers to use only one interface / service as an interface to the database you can intercept the method call. You could do it in the method itself split the list manually (or use the splitLongList utility method) but it is a bit against the DRY (don’t repeat yourself) principle. This is why I’ve written this article: to show the usage of Interceptors for this task.

The solution is simple: you intercept the method call with a big list and call the underlying database-method with a smaller list, which contains 1000 elements and you repeat this until you called the method with all the parameters. Naturally this can be only applied for queries which result in a List of elements because at the end of the interception you have to concatenate the result lists.

So let’s write an interceptor. It will contain the same functionality I mentioned in my earlier article with Spring aspects but for now I give you the sources at the end of the article.

Creating an interceptor does not need any special prerequisites. You create a simple Java class and add an interceptor method to it. In the case of the article it is an @AroundInvoke annotation:

public class BigListInterceptor {
    private static final int MAX_LIST_SIZE = 1000;
    public Object interceptBigList(final InvocationContext ctx) throws Exception {

As you can see, the difference between Spring and JavaEE is the parameter you get when using an Around advice: with Spring and AOP it is a ProceedingJoinPoint, with JavaEE it is an InvocationContext. And this invocation context makes it a bit easier to access the method signature — so we need less code for some validations.

To use the interceptor automatically you just have to annotate the method with the @Interceptor annotation and provide it the class which has the around advice.

public void handleOra01795(final List<Long> parameters) {

    Query query = entityManager.createQuery("FROM TestEntity WHERE id in (:ids)", String.class);
    query.setParameter("ids", parameters);

And this was it. If you call the handleOra01795 method with the big list as parameter you won’t get an exception. Instead if you say hibernate to output SQL statements you’d see something similar to this in your log:

15:19:55,665 INFO  [stdout] (http-localhost/ Hibernate: select testo_.ID as ID1_31_ from TEST_TABLE testo_ where testo_.ID in (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ...

15:20:03,546 INFO  [stdout] (http-localhost/ Hibernate: select testo_.ID as ID1_31_ from TEST_TABLE testo_ where testo_.ID in (?)

In line 1 I cropped the output but there were only 1000 question marks so the statement was executed. In line 3 is only the remaining one ID to query.

Conclusion and sources

If you do not want to use “magick” AOP in your Java EE system then you can have repository interfaces which get big lists as parameters and they reach them to the database layer — in smaller chunks.

The sources are now available in the ora01795 branch in my JavaEE repository. If you have any questions or comments feel free to open an issue at GitHub or mail me.

Share the knowledge!

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

%d bloggers like this: