ORA-12704: character set mismatch when performing multi-row INSERT of nullable NVARCHAR's : <p>Consider the following table where one of the columns is of type nullable <code>NVARCHAR</code>:</p>

<pre class="lang-sql prettyprint-override"><code>CREATE TABLE CHARACTER_SET_MISMATCH_TEST (
    ID NUMBER(10) NOT NULL,
    VALUE NVARCHAR2(32)
);
</code></pre>

<p>Now, I want to insert multiple data tuples into this table using the multi-row <code>INSERT</code> (with sub-query) syntax:</p>

<pre class="lang-sql prettyprint-override"><code>INSERT
    INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
    SELECT ?, ? FROM DUAL
    UNION ALL
    SELECT ?, ? FROM DUAL;
</code></pre>

<p>If <code>NVARCHAR</code> values are either both <code>NULL</code> or both non-<code>NULL</code>, everything runs fine and I observe exactly 2 rows inserted. If, however, I mix <code>NULL</code> and non-<code>NULL</code> values within a single <code>PreparedStatement</code>, I immediately receive an <code>ORA-12704: character set mismatch</code> error:</p>

<pre class="lang-none prettyprint-override"><code>java.sql.SQLException: ORA-12704: character set mismatch
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:452)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:400)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:884)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:471)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:199)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:535)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:238)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1385)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1709)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4364)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:4531)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:5575)
</code></pre>

<p>Here's the code which reproduces the issue:</p>

<pre class="lang-java prettyprint-override"><code>package com.example;

import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.MatcherAssert.assertThat;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;

import javax.sql.DataSource;

import org.eclipse.jdt.annotation.NonNull;
import org.eclipse.jdt.annotation.Nullable;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.BeforeMethod;
import org.testng.annotations.Test;

import oracle.jdbc.pool.OracleConnectionPoolDataSource;
import oracle.jdbc.pool.OracleDataSource;

public final class Ora12704Test {
    @NonNull
    private static final String SQL = "INSERT INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE) SELECT ?, ? FROM DUAL UNION ALL SELECT ?, ? FROM DUAL";

    @Nullable
    private static DataSource dataSource;

    @Nullable
    private Connection conn;

    @BeforeClass
    public static void setUpOnce() throws SQLException {
        dataSource = new OracleConnectionPoolDataSource();
        ((OracleDataSource) dataSource).setURL("jdbc:oracle:thin:@:1521:XE");
    }

    @BeforeMethod
    public void setUp() throws SQLException {
        this.conn = dataSource.getConnection("SANDBOX", "SANDBOX");
    }

    @AfterMethod
    public void tearDown() throws SQLException {
        if (this.conn != null) {
            this.conn.close();
        }
        this.conn = null;
    }

    @Test
    public void testNullableNvarchar()
    throws SQLException {
        try (final PreparedStatement pstmt = this.conn.prepareStatement(SQL)) {
            pstmt.setInt(1, 0);
            pstmt.setNString(2, "NVARCHAR");
            pstmt.setInt(3, 1);
            pstmt.setNull(4, Types.NVARCHAR);

            final int rowCount = pstmt.executeUpdate();
            assertThat(rowCount, is(2));
        }
    }
}
</code></pre>

<p>Strangely, the above unit test passes just fine if I explicitly cast my parameters to <code>NCHAR</code>:</p>

<pre class="lang-sql prettyprint-override"><code>INSERT
    INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
    SELECT ?, TO_NCHAR(?) FROM DUAL
    UNION ALL
    SELECT ?, TO_NCHAR(?) FROM DUAL;
</code></pre>

<p>or switch to the <code>INSERT ALL</code> syntax:</p>

<pre class="lang-sql prettyprint-override"><code>INSERT ALL
    INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
    VALUES (?, ?)
    INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
    VALUES (?, ?)
    SELECT * FROM DUAL;
</code></pre>

<p>But what's wrong with the original code?</p>
