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

Can you try to use following sql instead:

SELECT ?, cast(? as nvarchar2(32)) FROM DUALUNION ALLSELECT ?, 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.

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-- orINSERTINTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)SELECT 0, N'abc' FROM DUALUNION ALLSELECT 1, CAST(NULL AS VARCHAR2(100)) FROM DUAL;-- ORA-12704: character set mismatch

DBFiddle Demo

In Oracle if you do:

SELECT N'abc' FROM dualUNION ALLSELECT '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, "");

I recommend you three check.

First change this part:

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

to this:

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

(I think its not your problem. its only a recommend because it may solve some database character set problem)

Second check your connection pool character set: prefer to set "UTF-8".something like this spring.datasource.connectionProperties=useUnicode=true;characterEncoding=utf-8;

or may be you set it in application server or may be you handle it in the code.

Third you must check your insert statement with sql tools like plsql developer or ... and test this statement directly:

INSERT INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)SELECT 1, 'test' FROM DUALUNION ALLSELECT 2, null FROM DUAL;

or even this:

SELECT 1 aa, 'test' bb FROM DUALUNION ALLSELECT 2 aa, null bb FROM DUAL;

If you got the error again.its because your database character set and not related to your code.

I hope this help.