Hibernate could not fetch the SequenceInformation from the database

I solved the problem as follows. Created an extension for Oracle12cDialect. Limited the maximum/minimum value of columns to SQL

package ru.mvawork.hibernate;import org.hibernate.dialect.Oracle12cDialect;@SuppressWarnings("unused")public class CustomOracleDialect extends Oracle12cDialect {    @Override    public String getQuerySequencesString() {        return "select SEQUENCE_OWNER, SEQUENCE_NAME, greatest(MIN_VALUE,         -9223372036854775807) MIN_VALUE,\n"+                "Least(MAX_VALUE, 9223372036854775808) MAX_VALUE, INCREMENT_BY,     CYCLE_FLAG, ORDER_FLAG, CACHE_SIZE,\n"+                "Least(greatest(LAST_NUMBER, -9223372036854775807), 9223372036854775808) LAST_NUMBER,\n"+                "PARTITION_COUNT, SESSION_FLAG, KEEP_VALUE\n"+                "from all_sequences";    }}

In the application.properties file referred to a dialect implementation

spring.jpa.properties.hibernate.dialect=ru.mvawork.hibernate.CustomOracleDialect

You can recreate sequences by limiting the minimum and maximum values. In my case, I can 't do it. The primary keys that I use have the dimension Number (12), which falls within the range limit from -9223372036854775807 to 9223372036854775808 with a large margin

In the end, I came up to the following solution:

1. Create a sequence information extractor that extends SequenceInformationExtractorOracleDatabaseImpl:

public class AppSequenceInformationExtractor extends SequenceInformationExtractorOracleDatabaseImpl {   /**    * Singleton access    */   public static final AppSequenceInformationExtractor INSTANCE = new AppSequenceInformationExtractor();      @Override   protected Long resultSetMinValue(ResultSet resultSet) throws SQLException {      return resultSet.getBigDecimal("min_value").longValue();   }}

Yes, I understand that we can lose information about the overall magnitude and precision of this BigDecimal value as well as return a result with the opposite sign. But this is not important due to this Steve Ebersole's comment about the Long getMinValue() and Long getMaxValue() methods from the SequenceInformation interface:

I'm actually tempted to just drop these 2 methods from SequenceInformation. We never use them in any meaningful way. Or change the return type for these 2 methods from Long to BigInteger - it could be BigDecimal instead, but the value is implicitly an integer (in the whole number sense).

I guess at this point it is too late in the game to do either of these, so something like your change is fine - like I said, we never use these values anyway. We should definitely deprecate these 2 methods IMO.

So, this trick just allows to avoid the exception with minimal awkward extra coding.

2. Create a hibernate dialect that extends Oracle12cDialect:

public class AppOracleDialect extends Oracle12cDialect{   @Override   public SequenceInformationExtractor getSequenceInformationExtractor() {      return AppSequenceInformationExtractor.INSTANCE;   }      @Override   public String getQuerySequencesString() {      return "select * from user_sequences";   }}

3. And then use this dialect in the persistence.xml:

<property name="hibernate.dialect" value="com.my.app.AppOracleDialect" />

As for the method getQuerySequencesString() overriding and usage USER_SEQUENCES instead of ALL_SEQUENCES it's debatable (See HHH-13322 and HHH-14022). But, in my case, the USER_SEQUENCES usage is preferable.

You simple used the dafault MAX_VALUE of a sequence, which is too high for the Java LONG datatype.

Fortunatelly you may any time reset the MAX_VALUE with ALTER SEQUENCE to a lower number that will cause no problems.

Example

CREATE SEQUENCE SEQ_TEST START WITH 1 INCREMENT BY 1 NOCYCLE;select MAX_VALUE from ALL_SEQUENCES where SEQUENCE_NAME = 'SEQ_TEST'; MAX_VALUE----------9999999999999999999999999999ALTER SEQUENCE SEQ_TEST  MAXVALUE 9223372036854775807;select MAX_VALUE from ALL_SEQUENCES where SEQUENCE_NAME = 'SEQ_TEST'; MAX_VALUE----------9223372036854775807

and BTW

it looks strange that hibernate tries to read metadata about all sequences, not only about used in my application.

Hibernate uses select * from all_sequences as an Oracle Dialect to get the sequence information. Note that ALL_SEQUENCES does not meanall existing sequences, but all sequences, that your Hibernate database user (DBUSER from the connection pool) is granted to use - which is of course absolute correct.