title: Is H2 database compatible with Oracle 'Insert All' statement?
body: <p>I'm writing tests for MyBatis DAO layer for Oracle database and have a problem with batch insert operation. It is implemented with Oracle statement <code>INSERT ALL</code>:</p>
  
  <pre><code>&lt;insert id="batchInsertElements" parameterType="java.util.List" useGeneratedKeys="false"&gt;
      INSERT ALL
      &lt;foreach collection="list" item="element"&gt;
          INTO table1
          (
          col1, col2, col3, col4
          )
          VALUES
          (
          #{element.col1},
          (select nvl(c.ID, -1) from table2 c where c.name = #{element.col2}),
          #{element.col3},
          #{element.col4}
          )
      &lt;/foreach&gt;
      SELECT 1 FROM DUAL
  &lt;/insert&gt;
  </code></pre>
  
  <p>Against real database, it is working with no errors. For tests, I'm using H2 database and spring-dbunit. Configuration for test database:</p>
  
  <pre><code>&lt;bean id="testDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"&gt;
      &lt;property name="driverClassName" value="org.h2.Driver" /&gt;
      &lt;property name="url" value="jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;MODE=Oracle" /&gt;
      &lt;property name="username" value="sa" /&gt;
      &lt;property name="password" value="" /&gt;
  &lt;/bean&gt;
  </code></pre>
  
  <p>During a test for the batch insert I'm getting SQL syntax error:</p>
  
  <pre><code>org.springframework.jdbc.BadSqlGrammarException: 
  ### Error updating database.  Cause: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "INSERT ALL[*]
  
          INTO TABLE1
          (
          COL1, COL2, COL3, COL4
          )
          VALUES
          (
          ?,
          (SELECT NVL(C.ID, -1) FROM TABLE2 C WHERE C.NAME = ?),
          ?,
          ?
          )
  
      SELECT 1 FROM DUAL "; expected "INTO";
   [42001-193]; bad SQL grammar [];
  </code></pre>
  
  <p>I'm suspecting that <code>INSERT ALL</code> statement is not supported by H2 database. Is it so? Any workaround? Can HSQLDB be more suitable for Oracle DAO testing? </p>