org.hibernate.exception.SQLGrammarException: could not get next sequence value

Suppose, if a sequence named “SEQ_VENDOR_CATALOG_IMPORT_ID” is used in the class and if it does not exist in database, create the sequence and test the same.

After creating the sequence, make sure whether the sequence last value is greater than or equal with the number of records in the required table.

For example,

	@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "CATL_IMP_SEQ_GEN")
	@javax.persistence.SequenceGenerator(name = "CATL_IMP_SEQ_GEN", sequenceName = "SEQ_VENDOR_CATALOG_IMPORT_ID", allocationSize = 1)
	@Column(name = "VENDOR_CATALOG_IMPORT_ID", unique = false, length = 12)

In the above example, if the sequence  SEQ_VENDOR_CATALOG_IMPORT_ID does not exist, we will end with the below exception.

[CAR] [2013-11-11 02:04:23,369] WARN [http-8010-4] JDBCExceptionReporter.logExceptions(77) | SQL Error: 2289, SQLState: 42000
[CAR] [2013-11-11 02:04:23,370] ERROR [http-8010-4] JDBCExceptionReporter.logExceptions(78) | ORA-02289: sequence does not exist
[CAR] [2013-11-11 02:04:23,373] WARN [http-8010-4] LoadContexts.cleanup(108) | fail-safe cleanup (collections) : org.hibernate.engine.loading.CollectionLoadContext@79ce7efa<rs=org.apache.commons.dbcp.DelegatingResultSet@8214ea8>
[CAR] [2013-11-11 02:04:23,374] WARN [http-8010-4] LoadContexts.cleanup(108) | fail-safe cleanup (collections) : org.hibernate.engine.loading.CollectionLoadContext@492bda8c<rs=org.apache.commons.dbcp.DelegatingResultSet@f522117>
[CAR] [2013-11-11 02:04:23,384] ERROR [http-8010-4] VendorImageXMLUtil.readAndProcessVendorImageCheckFeedbackDetails(498) | error while processing the VendorImage org.springframework.dao.InvalidDataAccessResourceUsageException: could not get next sequence value; nested exception is <strong>org.hibernate.exception.SQLGrammarException: could not get next sequence value</strong>

Once created, the id which is generated through this particular sequence must be verified. The id last value is same as the last value generated by sequence.

For example, if sequence last value is 10 and id last value is 1000, then we will end with Unique Key constraint exception:

Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (CARS_DEV2.RRD_IMAGE_CHECK_FEEDBACK_PK) violated
        at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(
        at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(
        at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(
        at org.apache.commons.dbcp.DelegatingStatement.executeBatch(
        at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(
        ... 96 more
[CAR] [2013-11-11 05:28:03,372] ERROR [http-8010-1] VendorImageXMLUtil.readAndProcessVendorImageCheckFeedbackDetails(498) | error while processing the VendorImage org.springframework.dao.DataIntegrityViolationException: Could not execute JDBC batch update; nested exception is org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update

To overcome this exception, alter the sequence last value with the last value of the id.

Share This Post

Recent Articles

Leave a Reply

© 2018 Techy Diary. All rights reserved.
Powered by Charvi Groups