In-memory database supporting proprietary INSERT ALL oracle syntax
<p>I need to write a simple query using oracle, java and mybatis:</p>

<pre><code>select * from FOO foo where foo.id IN (ids)
</code></pre>

<p>Now, <code>ids</code> is a large collection of strings, about 7000. Unfortunately, oracle has 1000 elements limit for IN clause. </p>

<p>To overcome this, I can either:</p>

<ol>
<li>concatenate the query dynamically so it becomes:
<code>select * from FOO foo where foo.id IN (chunk1) or foo.id IN (chunk2) ...</code>
I'm not sure if it even works and I really doubt it performs well </li>
<li>use temporary table and rewrite the query to: <code>select * from FOO foo join SOME_TEMPORARY_ID tempids on foo.id = tempids.id</code>. </li>
</ol>

<p>I've decided to go for 2 option. Before performing the query I need to somehow do an efficient batch insert to Oracle. Unfortunately, Oracle has proprietary syntax to do batch inserts:</p>

<pre><code>INSERT ALL
INTO some_table VALUES ('foo')
INTO some_table VALUES ('foo1')
INTO some_table VALUES ('foo2')
....
INTO some_table VALUES ('foo12345')
SELECT * FROM DUAL
</code></pre>

<p>Now, I didn't mention but I want to write an integration test for this, ideally using H2 or any other inmemory database.
Of course H2 doesn't support that syntax. Neither does HSQLDB. </p>

<p>Do you know any in-memory database that fully supports proprietary oracle syntax? Or at least this specific one INSERT ALL clause?</p>