Question: Make Oracle last_day function be compatible with H2 database

Question

Make Oracle last_day function be compatible with H2 database

Answers 0
Added at 2016-12-29 10:12
Tags
Question

I am writing integration testing and I want to test my sql queries by using a H2 database. In production these queries are running against an Oracle database.

I have to run this query

I have tries to use the compatibility mode

SELECT last_day(MY_CURRENT_DATE) from MY_TIME

by using the oracle compatibility mode jdbc:h2:mem:default;MODE=Oracle;DB_CLOSE_DELAY=-1 but I receive this error:

org.h2.jdbc.JdbcSQLException: Function "LAST_DAY" not found; SQL statement:
SELECT last_day(MY_CURRENT_DATE) from MY_TIME [90022-176]

It just seems that the compatibility mode does not cover the vendor-specific functions.

I have an idea how to solve this in testing. Just override the Oracle last_day function with a H2 user-defined function:

DROP ALIAS IF EXISTS last_day;

CREATE ALIAS last_day AS $$
String last_day(java.sql.Connection con, Date date) throws Exception {
        //get the last day of month
  }
}
$$;

But I am wondering if it is the best idea? Is there a way to re-write the Oracle LAST_DAY function (that calculates the last day of month) in a way which is compatible with all other databases (including H2)?

Answers to

Make Oracle last_day function be compatible with H2 database

Source Show
◀ Wstecz