How to use Oracle scripts and functions in H2 Database using Java


In this use case we have a production server that is Oracle and we use H2 as development server / tests for a Spring Boot project.

We are using H2 as local or memory database to quickly develop and test functionalities but we want to use the same data that is present in the Oracle instance.

In our case we exported the data from Oracle using IntelliJ. IntelliJ generated some scripts that are compatible with Oracle but not with H2, in particular the dates and timestamps used 'TO_DATE' and 'TO_TIMESTAMP' functions, these are specific to Oracle.

How to use TO_DATE and TO_TIMESTAMP in H2

This solution can be applied to other functions of Oracle but not present in H2.

In H2 you can create aliases using Java, here the ALIAS documentation.

The Oracle SQL script contained something like:

INSERT INTO H2_TABLE(POST_DATE, COMMENT_TIMESTAMP) 
VALUES(TO_DATE('2021-12-31', "YYYY-MM-DD HH24:MI:SS"), TO_TIMESTAMP('2022-12-31 22:23:24.123456', 'YYYY-MM-DD HH24:MI:SS.FF6')); 

The date/time converters contain the value and the pattern of the format used to insert in Oracle.

The DBException in H2

If you run this script in H2 you will receive an exception like:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Function "TO_DATE" not found; SQL Statement: 
INSERT ... 
    at org.h2.message.DBException.getJdbcSQLException(DbException.java.632) [h2...jar:...] 

Create TO_DATE for H2 Database using Java

As we said, H2 Database is very flexible and we can create functions using Java.

In our data.sql script (we are using Spring Boot and Spring Data), we add:

drop ALIAS if exists TO_DATE; 
CREATE ALIAS TO_DATE as ' 
import java.text.*; 
@CODE 
java.util.Date toDate(String originalDate, String dateFormat) throws Exception { 
    return new SimpleDateFormat("yyyy-MM-dd").parse(originalDate); 
} 
'; 

In our example we did very simple and we hard-coded the pattern of the date, the dateFormat is specific to Oracle and not very interesting for us.
You can adapt the script as it's better for your configuration.

Create TO_TIMESTAMP for H2 Database using Java

The code for TO_TIMESTAMP is similar to the code used for TO_DATE:

drop ALIAS if exists TO_TIMESTAMP; 
CREATE ALIAS TO_TIMESTAMP as ' 
import java.text.*; 
@CODE 
java.sql.Timestamp toTimestamp(String originalTime, String dateFormat) throws Exception { 
    return new java.sql.Timestamp(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(originalTime).getTime()); 
} 
'; 

Declaring these scripts before the INSERT INTO in your data.sql allow to simulate the TO_DATE and TO_TIMESTAMP functions of Oracle in H2 and reuse the same scripts.

Further customization

If you have errors with other functions you can simply implement them with Java as for our examples.

Note that H2 has an Oracle mode: jdbc:h2:~/test;MODE=Oracle or in the SQL SET MODE Oracle that increases the compatibility with Oracle.
Here you can find the documentation of the Oracle mode in H2


You could be interested in

Angular: Local proxy server to improve frontend development

Mix local and remote REST answers to speed up frontend dev
2019-11-08

WebApp built by Marco using SpringBoot, Java 17, Mustache, Markdown and in Azure