Spring boot H2 db mode oracle

Question

Hi I have a spring boot app (2.3.1.RELEASE, ojdbc8) and basically it is connected to an oracle database.

The spring boot app starts find when it is connected to the oracle db.However fails to start when the integration test is connected to a H2 embedded db and some of my queries are failing with the following error message:

Caused by: java.lang.NullPointerException: null
 at org.hibernate.hql.internal.NameGenerator.generateColumnNames(NameGenerator.java:27)
 at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.generateColumnNames(SessionFactoryHelper.java:434)

I manage to make it work by adding adding the following line of code in my application-test.yml file:

spring:
 jpa:
 database-platform: org.hibernate.dialect.Oracle10gDialect

Please fine below application-test.yml config:

spring.datasource:
 url: jdbc:h2:mem:db;Mode=Oracle;DB_CLOSE_DELAY=-1
 username: sa
 password: sa
 driverClassName: org.h2.Driver
# added empty context path to override application.yml context path
server:
 servlet:
 contextPath:
spring:
 jpa:
 database-platform: org.hibernate.dialect.Oracle10gDialect

The application starts well and integration completed successfully however the following exception can be shown in the log:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "ALL_SEQUENCES" not found; SQL statement:
select * from all_sequences [42102-200]
 at org.h2.message.DbException.getJdbcSQLException(DbException.java:453) ~[h2-1.4.200.jar:1.4.200]
 at org.h2.message.DbException.getJdbcSQLException(DbException.java:429) ~[h2-1.4.200.jar:1.4.200]
 at org.h2.message.DbException.get(DbException.java:205) ~[h2-1.4.200.jar:1.4.200]
 at org.h2.message.DbException.get(DbException.java:181) ~[h2-1.4.200.jar:1.4.200]
 at org.h2.command.Parser.readTableOrView(Parser.java:7628) ~[h2-1.4.200.jar:1.4.200]
 at org.h2.command.Parser.readTableFilter(Parser.java:1970) ~[h2-1.4.200.jar:1.4.200]
 at org.h2.command.Parser.parseSelectFromPart(Parser.java:2827) ~[h2-1.4.200.jar:1.4.200]
 at org.h2.command.Parser.parseSelect(Parser.java:2959) ~[h2-1.4.200.jar:1.4.200]
 
 
 ....
 
 org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "drop table dash_processed cascade constraints" via JDBC Statement
 at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
 at org.hibernate.tool.schema.internal.SchemaDropperImpl.applySqlString(SchemaDropperImpl.java:375) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
 at org.hibernate.tool.schema.internal.SchemaDropperImpl.applySqlStrings(SchemaDropperImpl.java:359) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
 at org.hibernate.tool.schema.internal.SchemaDropperImpl.dropFromMetadata(SchemaDropperImpl.java:241) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
 at org.hibernate.tool.schema.internal.SchemaDropperImpl.performDrop(SchemaDropperImpl.java:154) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
 at org.hibernate.tool.schema.internal.SchemaDropperImpl.doDrop(SchemaDropperImpl.java:126) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
 at org.hibernate.tool.schema.internal.SchemaDropperImpl.doDrop(SchemaDropperImpl.java:112) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
 at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:145) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
 at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
 
 ...
 
 drop table dash_processed cascade constraints [42102-200]
 at org.h2.message.DbException.getJdbcSQLException(DbException.java:453) ~[h2-1.4.200.jar:1.4.200]
 at org.h2.message.DbException.getJdbcSQLException(DbException.java:429) ~[h2-1.4.200.jar:1.4.200]
 at org.h2.message.DbException.get(DbException.java:205) ~[h2-1.4.200.jar:1.4.200]
 at org.h2.message.DbException.get(DbException.java:181) ~[h2-1.4.200.jar:1.4.200]
 at org.h2.command.ddl.DropTable.prepareDrop(DropTable.java:65) ~[h2-1.4.200.jar:1.4.200]
 at org.h2.command.ddl.DropTable.update(DropTable.java:124) ~[h2-1.4.200.jar:1.4.200]

Any idea how i can stop those errors?

Thanks in advance

Answer 1

To simulate the Oracle and sequence objects with h2 database add this line to your spring boot application.properties:

spring.datasource.url=jdbc:h2:mem:testdb;Mode=Oracle

h2 doc Oracle Compatibility Mode section

• Krzysztof Krasoń: This does not work, because H2 doesn't provide ALL_SEQUENCES table in Oracle mode