ORA-12704: character set mismatch when performing multi-row INSERT of nullable NVARCHAR's

Question

Consider the following table where one of the columns is of type nullable NVARCHAR:

CREATE TABLE CHARACTER_SET_MISMATCH_TEST (
    ID NUMBER(10) NOT NULL,
    VALUE NVARCHAR2(32)
);

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

INSERT
    INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
    SELECT ?, ? FROM DUAL
    UNION ALL
    SELECT ?, ? FROM DUAL;

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

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)

Here's the code which reproduces the issue:

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));
        }
    }
}

Strangely, the above unit test passes just fine if I explicitly cast my parameters to NCHAR:

INSERT
    INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
    SELECT ?, TO_NCHAR(?) FROM DUAL
    UNION ALL
    SELECT ?, TO_NCHAR(?) FROM DUAL;

or switch to the INSERT ALL syntax:

INSERT ALL
    INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
    VALUES (?, ?)
    INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
    VALUES (?, ?)
    SELECT * FROM DUAL;

But what's wrong with the original code?

Answer 1

If you could intercept actual query that is sent to DB I guess it looks similiar to:

INSERT
    INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
    SELECT 0, 'abc' FROM DUAL
    UNION ALL
    SELECT 1, CAST(NULL AS NVARCHAR2(100)) FROM DUAL;
-- ORA-12704: character set mismatch

-- or
INSERT
INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
SELECT 0, N'abc' FROM DUAL
UNION ALL
SELECT 1, CAST(NULL AS VARCHAR2(100)) FROM DUAL;
-- ORA-12704: character set mismatch

DBFiddle Demo

---

In Oracle if you do:

SELECT N'abc' FROM dual
UNION ALL
SELECT 'abc' FROM dual

You will get error:

ORA-12704: character set mismatch

From UNION ALL doc:

If component queries select character data, then the datatype of the return values are determined as follows:

• If both queries select values of datatype CHAR of equal length, then the returned values have datatype CHAR of that length. If the queries select values of CHAR with different lengths, then the returned value is VARCHAR2 with the length of the larger CHAR value.

• If either or both of the queries select values of datatype VARCHAR2, then the returned values have datatype VARCHAR2.

So returning to your working approaches:

1) Same data type(explicit conversion)

INSERT
    INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
    SELECT ?, TO_NCHAR(?) FROM DUAL
    UNION ALL
    SELECT ?, TO_NCHAR(?) FROM DUAL;

2) Two "independent" INSERTs :

INSERT ALL
    INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
    VALUES (?, ?)
    INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
    VALUES (?, ?)
    SELECT * FROM DUAL;

3) "If NVARCHAR values are either both NULL or both non-NULL, everything runs fine and I observe exactly 2 rows inserted" - same data type so it works fine

INSERT
    INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
    SELECT ?, ? FROM DUAL
    UNION ALL
    SELECT ?, ? FROM DUAL;

---

Finally case where there is NULL and NOT NULL value will generate error. It clearly indicates that mapping is not valid. I believe it is related to:

Valid SQL-JDBC Data Type Mappings:

┌────────────────────────┬──────────────────────────────────────────┐
│ These SQL data types:  │ Can be materialized as these Java types: │
├────────────────────────┼──────────────────────────────────────────┤
│ NVARCHAR2              │ no (see Note)                            │
└────────────────────────┴──────────────────────────────────────────┘

Note: The types NCHAR and NVARCHAR2 are supported indirectly. There is no corresponding java.sql.Types type, but if your application calls formOfUse(NCHAR), then these types can be accessed.

And NCHAR, NVARCHAR2, NCLOB and the defaultNChar Property in JDK 1.5:

By default, the oracle.jdbc.OraclePreparedStatement interface treats the data type of all the columns in the same way as they are encoded in the database character set. However, since Oracle Database 10g, if you set the value of oracle.jdbc.defaultNChar system property to true, then JDBC treats all character columns as being national-language.

The default value of defaultNChar is false. If the value of defaultNChar is false, then you must call the setFormOfUse(, OraclePreparedStatement.FORM_NCHAR) method for those columns that specifically need national-language characters.

So your could will look like:

pstmt.setInt(1, 0);
pstmt.setFormOfUse(2, OraclePreparedStatement.FORM_NCHAR);
pstmt.setNString(2, "NVARCHAR");
pstmt.setInt(3, 1);
pstmt.setFormOfUse(4, OraclePreparedStatement.FORM_NCHAR);
pstmt.setNull(4, Types.NVARCHAR);

---

One more thought: Oracle treats empty string same as NULL so below code should also work fine:

pstmt.setInt(1, 0);
pstmt.setNString(2, "NVARCHAR");
pstmt.setInt(3, 1);
pstmt.setNString(4, "");

Answer 2

Can you try to use following sql instead:

SELECT ?, cast(? as nvarchar2(32)) FROM DUAL
UNION ALL
SELECT ?, cast(? as nvarchar2(32)) FROM DUAL;

I think your error because null by default is varchar2 type and there is type mismatch in union all part of your sql. Btw to check that you can run this sql without insert part and see if error still exits or not.