Numeric Overflow exception when calling findRevisions() or even custom audit query - spring-data-jpa with oracle

Question

I'm struggling with exception since the past 4 days and being completely frustrated with it that I'm asking this question, I have referred various answers already on StackoverOverflow but they dont seem to have the issue as mine, so please don't mark it as duplicate.

Hibernate could not fetch the SequenceInformation from the database

I have spring data jpa 2.4.4 version and I'm using spring data envers with spring boot on oracle database, I have ojdbc 7 as a dependency.

All my tables are audited using envers and generated automatically by confirguration, so Im facing Numeric overflow exception when Im trying to fetch records whose rev is Number(10), my rev table has 2 columns where rev is a Number(10) digits precision.

REVINFO table is auto generated by spring data envers and i have not customised it in my code.

REVINFO table columns :
REV            NUMBER(10)
REVTIMESTAMP   TIMESTAMP

Main Table - Immigration_file

@Entity
@Audited
@NoArgsConstructor
@AllArgsConstructor
public class ImmigrationFile extends BaseEntity implements ImmigrationFileAware<ImmigrationFile>,Serializable, Cloneable {

    @Id
    @SequenceGenerator(name = "IMMIGRATION_FILE_ID_GENERATOR",
            sequenceName = "SEQ_IMMIGRATION_FILE_ID",
            allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "IMMIGRATION_FILE_ID_GENERATOR")
    @Column(name = "ID", unique = true, nullable = false)
    private Long immigrationFileId;

...other columns
}

Audit table - Immigration_file_hist, having all columns of main table along with REV and REVTYPE, so the error is being thrown when the value of REV is "4686710314".

I have tried both the approached of using RevisionRepository and custom Audit Query and I get the same exception with both of them code to fetch the revisions of immigration_file_hist repository

@Repository
public interface ImmigrationFileDaoRepository extends RevisionRepository<ImmigrationFile, Long, BigInteger>, JpaRepository<ImmigrationFile, Long> {

        Page<Revision<BigInteger, ImmigrationFile>> revisions = immigrationFileDaoRepository.findRevisions(immigrationFile.getImmigrationFileId(), PageRequest.of(Integer.valueOf(startPageIndex), Integer.valueOf(endPageIndex), RevisionSort.desc()));

I was using Long earlier and i have changed it to BigInteger and i still face the same issue.

Custom Audit Query

public List<ImmigrationFile> findRevisions(Long immigrationFileId) {
        AuditReader reader = AuditReaderFactory.get(entityManager);
        List<ImmigrationFile> results = reader
                .createQuery()
                .forRevisionsOfEntity(ImmigrationFile.class, true, false)
                .add(AuditEntity.id().eq(immigrationFileId))
                .addOrder(AuditEntity.property("version").desc())
                .setMaxResults(maxResult)
                .getResultList();
        return results;

Im getting the below exception :

 Caused by: org.springframework.orm.jpa.JpaSystemException: could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:331)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:548)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:174)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
    at com.sun.proxy.$Proxy252.findRevisions(Unknown Source)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:687)
    ... 82 common frames omitted
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.loader.Loader.doList(Loader.java:2853)
    at org.hibernate.loader.Loader.doList(Loader.java:2832)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2664)
    at org.hibernate.loader.Loader.list(Loader.java:2659)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1414)/
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1565)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1533)
    at org.hibernate.envers.query.internal.impl.AbstractAuditQuery.buildAndExecuteQuery(AbstractAuditQuery.java:106)
    at org.hibernate.envers.query.internal.impl.RevisionsOfEntityQuery.getQueryResults(RevisionsOfEntityQuery.java:173)
    at org.hibernate.envers.query.internal.impl.RevisionsOfEntityQuery.list(RevisionsOfEntityQuery.java:136)
    at org.hibernate.envers.query.internal.impl.AbstractAuditQuery.getResultList(AbstractAuditQuery.java:112)
    at org.springframework.data.envers.repository.support.EnversRevisionRepositoryImpl.findRevisions(EnversRevisionRepositoryImpl.java:155)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:289)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)
    at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:524)
    at org.springframework.data.repository.core.support.Reposito.ryComposition.invoke(RepositoryComposition.java:285)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:531)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:156)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:131)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:371)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:134)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
    ... 97 common frames omitted
Caused by: java.sql.SQLException: Numeric Overflow
    at oracle.jdbc.driver.NumberCommonAccessor.throwOverflow(NumberCommonAccessor.java:4139)
    at oracle.jdbc.driver.NumberCommonAccessor.getInt(NumberCommonAccessor.java:125)
    at oracle.jdbc.driver.GeneratedStatement.getInt(GeneratedStatement.java:199)
    at oracle.jdbc.driver.GeneratedScrollableResultSet.getInt(GeneratedScrollableResultSet.java:246)
    at oracle.jdbc.driver.GeneratedResultSet.getInt(GeneratedResultSet.java:556)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.getInt(WSJdbcResultSet.java:1089)
    at org.hibernate.type.descriptor.sql.IntegerTypeDescriptor$2.doExtract(IntegerTypeDescriptor.java:62)
    at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:243)
    at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:329)
    at org.hibernate.type.ManyToOneType.hydrate(ManyToOneType.java:184)
    at org.hibernate.type.ComponentType.hydrate(ComponentType.java:670)
    at org.hibernate.loader.Loader.extractKeysFromResultSet(Loader.java:803)
    at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:727)
    at org.hibernate.loader.Loader.getRowsFromResultSet(Loader.java:1039)
    at org.hibernate.loader.Loader.processResultSet(Loader.java:990)
    at org.hibernate.loader.Loader.doQuery(Loader.java:959)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
    at org.hibernate.loader.Loader.doList(Loader.java:2850)
    ... 131 common frames omitted

Answer 1

A revision number(REV) value should always be increasing and never overflows.

The default implementations provided by Envers use an int data type that has an upper bound of Integer.MAX_VALUE.

Since you have already mentioned that your REV number has the value of 4686710314 and it exceeds this limit, that's why you are getting a Numeric Overflow exception.

To resolve this issue, you can modify the data type of the REV column in the REVINFO table to accommodate larger values. But this is not possible in your case as the REVINFO is auto-generated.

So you should consider using a custom revision entity mapping using a long data type.

Below is the implementation

@Entity
@Table(name = "REV_INFO")
@RevisionEntity
public  class CustomRevisionEntity implements Serializable {

    private static final long serialVersionUID = 8530213963961662300L;

    @Id
    @GeneratedValue
    @RevisionNumber
    @Column(name = "REV")
    private long id;

    @RevisionTimestamp
    @Column(name = "REVTSTMP")
    private long timestamp;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    @Transient
    public Date getRevisionDate() {
        return new Date( timestamp );
    }

    public long getTimestamp() {
        return timestamp;
    }

    public void setTimestamp(long timestamp) {
        this.timestamp = timestamp;
    }
}

And then RevisionRepository<ImmigrationFile, Long, Long>

Note: Verify the autogenerated REVINFO table REV column datatype length in Oracle.

Answer 2

Do not use int or long for identifiers and numbers retrieved from database. Most likely you do not perform any mathematical operations with them anyway.

Oracle stores numbers in decimal format, the corresponding type in Java is BigDecimal.

Your stack trace shows it:

at oracle.jdbc.driver.GeneratedResultSet.getInt(GeneratedResultSet.java:556)
at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.getInt(WSJdbcResultSet.java:1089)

You're calling getInt() on ResultSet, you should call getBigDecimal()