got ORA-18716: {0} not in any time zone.DATE when getting data from atp database

[stanleyw2014]

Issue description

I got below exception when trying to get data from atp database, tiimezone in database is UTC: SELECT DBTIMEZONE FROM DUAL; +00:00

And I tried to add jpa.default.properties.hibernate.jdbc.time_zone=UTC in application.properties, issue remains.

org.hibernate.exception.GenericJDBCException: Could not extract column [2] from JDBC ResultSet [ORA-18716: {0} not in any time zone.DATE https://docs.oracle.com/error-help/db/ora-18716/] [n/a] at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:63) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94) at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.getCurrentRowValue(JdbcValuesResultSetImpl.java:310) at org.hibernate.sql.results.internal.RowProcessingStateStandardImpl.getJdbcValue(RowProcessingStateStandardImpl.java:119) at org.hibernate.sql.results.graph.basic.BasicResultAssembler.extractRawValue(BasicResultAssembler.java:52) at org.hibernate.sql.results.graph.basic.BasicResultAssembler.assemble(BasicResultAssembler.java:59) at org.hibernate.sql.results.graph.DomainResultAssembler.assemble(DomainResultAssembler.java:33) at org.hibernate.sql.results.graph.entity.AbstractEntityInitializer.extractConcreteTypeStateValues(AbstractEntityInitializer.java:1081) at org.hibernate.sql.results.graph.entity.AbstractEntityInitializer.initializeEntityInstance(AbstractEntityInitializer.java:838) at org.hibernate.sql.results.graph.entity.AbstractEntityInitializer.initializeEntity(AbstractEntityInitializer.java:813) at org.hibernate.sql.results.graph.entity.AbstractEntityInitializer.initializeInstance(AbstractEntityInitializer.java:799) at org.hibernate.sql.results.internal.InitializersList.initializeInstance(InitializersList.java:70) at org.hibernate.sql.results.internal.StandardRowReader.coordinateInitializers(StandardRowReader.java:109) at org.hibernate.sql.results.internal.StandardRowReader.readRow(StandardRowReader.java:86) at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:204) at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:33) at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:209) at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:83) at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:76) at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:65) at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$2(ConcreteSqmSelectQueryPlan.java:137) at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:359) at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:300) at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:509) at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:427) at org.hibernate.query.Query.getResultList(Query.java:120) at io.micronaut.data.hibernate.operations.HibernateJpaOperations$FirstResultCollector.getFirst(HibernateJpaOperations.java:715) at io.micronaut.data.hibernate.operations.HibernateJpaOperations$FirstResultCollector.collect(HibernateJpaOperations.java:708) at io.micronaut.data.hibernate.operations.HibernateJpaOperations$FirstResultCollector.collect(HibernateJpaOperations.java:689) at io.micronaut.data.hibernate.operations.AbstractHibernateOperations.collectResults(AbstractHibernateOperations.java:405) at io.micronaut.data.hibernate.operations.AbstractHibernateOperations.collectFindOne(AbstractHibernateOperations.java:325) at io.micronaut.data.hibernate.operations.HibernateJpaOperations.lambda$findOne$3(HibernateJpaOperations.java:259) at io.micronaut.data.hibernate.operations.HibernateJpaOperations.lambda$executeRead$21(HibernateJpaOperations.java:546) at io.micronaut.transaction.support.AbstractPropagatedStatusTransactionOperations.lambda$execute$2(AbstractPropagatedStatusTransactionOperations.java:68) at io.micronaut.transaction.TransactionCallback.apply(TransactionCallback.java:37) at io.micronaut.transaction.support.AbstractTransactionOperations.executeTransactional(AbstractTransactionOperations.java:333) at io.micronaut.transaction.support.AbstractTransactionOperations.executeWithNewTransaction(AbstractTransactionOperations.java:318) at io.micronaut.transaction.support.AbstractTransactionOperations.executeNew(AbstractTransactionOperations.java:235) at io.micronaut.transaction.support.AbstractTransactionOperations.doExecute(AbstractTransactionOperations.java:137) at io.micronaut.transaction.support.AbstractTransactionOperations.lambda$doExecute$0(AbstractTransactionOperations.java:122) at io.micronaut.data.connection.support.AbstractConnectionOperations.executeWithNewConnection(AbstractConnectionOperations.java:143) at io.micronaut.data.connection.support.AbstractConnectionOperations.execute(AbstractConnectionOperations.java:90) at io.micronaut.transaction.support.AbstractTransactionOperations.doExecute(AbstractTransactionOperations.java:120) at io.micronaut.transaction.support.AbstractPropagatedStatusTransactionOperations.execute(AbstractPropagatedStatusTransactionOperations.java:65) at io.micronaut.transaction.TransactionOperations.executeRead(TransactionOperations.java:76) at io.micronaut.data.hibernate.operations.HibernateJpaOperations.executeRead(HibernateJpaOperations.java:546) at io.micronaut.data.hibernate.operations.HibernateJpaOperations.findOne(HibernateJpaOperations.java:254) at io.micronaut.data.runtime.intercept.DefaultFindOptionalInterceptor.intercept(DefaultFindOptionalInterceptor.java:47) at io.micronaut.data.runtime.intercept.DefaultFindOptionalInterceptor.intercept(DefaultFindOptionalInterceptor.java:34) at io.micronaut.data.runtime.intercept.DataIntroductionAdvice.intercept(DataIntroductionAdvice.java:83) at io.micronaut.aop.chain.MethodInterceptorChain.proceed(MethodInterceptorChain.java:137) at com.oracle.engage.config.channel.db.MessagingChannelsRepository$Intercepted.findById(Unknown Source) at com.oracle.engage.config.channel.controllers.MessagingConfigController.getChannel(MessagingConfigController.java:164) at com.oracle.engage.config.channel.controllers.$MessagingConfigController$Definition$Exec.dispatch(Unknown Source) at io.micronaut.context.AbstractExecutableMethodsDefinition$DispatchedExecutableMethod.invokeUnsafe(AbstractExecutableMethodsDefinition.java:447) at io.micronaut.context.DefaultBeanContext$BeanContextUnsafeExecutionHandle.invokeUnsafe(DefaultBeanContext.java:4274) at io.micronaut.web.router.AbstractRouteMatch.execute(AbstractRouteMatch.java:236) at io.micronaut.http.server.RouteExecutor.executeRouteAndConvertBody(RouteExecutor.java:488) at io.micronaut.http.server.RouteExecutor.lambda$callRoute$6(RouteExecutor.java:465) at io.micronaut.core.execution.ExecutionFlow.lambda$async$1(ExecutionFlow.java:87) at io.micronaut.core.propagation.PropagatedContext.lambda$wrap$3(PropagatedContext.java:211) at io.micrometer.core.instrument.composite.CompositeTimer.record(CompositeTimer.java:141) at io.micrometer.core.instrument.Timer.lambda$wrap$0(Timer.java:193) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:833) Caused by: java.sql.SQLException: ORA-18716: {0} not in any time zone.DATE https://docs.oracle.com/error-help/db/ora-18716/ at oracle.jdbc.driver.TimestampAccessor.getOffsetDateTime(TimestampAccessor.java:158) at oracle.jdbc.driver.Redirector$33.redirect(Redirector.java:840) at oracle.jdbc.driver.Redirector$33.redirect(Redirector.java:832) at oracle.jdbc.driver.Representation.getObject(Representation.java:581) at oracle.jdbc.driver.Accessor.getObject(Accessor.java:1030) at oracle.jdbc.driver.OracleStatement.getObject(OracleStatement.java:7331) at oracle.jdbc.driver.InsensitiveScrollableResultSet.getObject(InsensitiveScrollableResultSet.java:686) at com.zaxxer.hikari.pool.HikariProxyResultSet.getObject(HikariProxyResultSet.java) at org.hibernate.type.descriptor.jdbc.TimestampUtcAsOffsetDateTimeJdbcType$2.doExtract(TimestampUtcAsOffsetDateTimeJdbcType.java:111) at org.hibernate.type.descriptor.jdbc.BasicExtractor.extract(BasicExtractor.java:44) at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.getCurrentRowValue(JdbcValuesResultSetImpl.java:302) ... 63 common frames omitted

[radovanradic]

Could be related to this Hibernate issue This might help resolve it https://docs.jboss.org/hibernate/orm/6.2/migration-guide/migration-guide.html#ddl-offset-time

[stanleyw2014]

so I shoud add jpa.default.properties.hibernate. timezone.default_storage= NORMALIZE in application.properties of my project?

[radovanradic]

Yes

[stanleyw2014]

the issue remains after adding jpa.default.properties.hibernate.timezone.default_storage=NORMALIZE to application.properties of my micronaut project. any other workaround? just check the hibernat verion, it's 6.4.2, any other advice?

[graemerocher]

try some of the other hints on the Hibernate JIRA since this is a Hibernate issue

[radovanradic]

As Graeme suggested, looking at this discussion in Hibernate JIRA and this solution seems to be working for this issue. Adding this to application.properties should fix it

jpa.default.properties.hibernate.type.preferred_instant_jdbc_type=TIMESTAMP

[stanleyw2014]

thanks Radovan, this solution works! close this issue.