How to use Oracle as an embedded database for running unit tests in maven?

Question

I have an application for which the prod/test/qa database is only Oracle. Furthermore, I suspect SQL queries to be Oracle-specific. And unfortunatly, this application has second to no unit tests.

As a consequence, I would like to implement to unit tests, specifically regarding the search component (which obviously performs lots of Oracle operations, including table creation).

Usually, when I want to run tests, I start an embedded database (HSQL, as an example), run my tests on that database, and let it fade away when my tests end.

Due to my lack of confidence on the standardization level of this application, I would prefer to run tests on an Oracle database. As a consequence, I would like to start oracle database when my tests start, fill it with some test datan and stop it on test end. How can I do that in a maven context ?

Answer 1

I understand you want Oracle, but you may also try with h2 using oracle mode flag

jdbc:h2:~/test;MODE=Oracle

I have the same need in most of my projects, and this is the closest to Oracle I found. You can define aliases for the few missing functions.

Answer 2

You can use exec-maven-plugin to start any application during Maven lifecycle, including a full Oracle instance. However, you still need to have it installed on the builder, etc. which complicates the solution quite a bit.

So what we've done in another project:

1. Define a unique build name as Maven property (default to username for local builds; let CI provide this via -D, e.g., on Bamboo you can include build plan key and number)
2. Connect to a shared Oracle instance on our infrastructure
3. Create a new scheme based on the defined build name
4. Update the new empty scheme with DDL (we use Flyway in production, so this part is easy)
5. Run integration tests
6. Drop scheme in post-integration-test phase (since builds can be stopped manually or hang, it's still necessary to have some kind of automatic DB cleanup for stale schemes)

You can use sql-maven-plugin for executing SQL scripts. Or flyway-maven-plugin if using Flyway.