考虑下表，其中一列的类型可以为 null NVARCHAR:
CREATE TABLE CHARACTER_SET_MISMATCH_TEST (
ID NUMBER(10) NOT NULL,
VALUE NVARCHAR2(32)
);
现在，我想使用多行 INSERT(带子查询)语法将多个数据元组插入到此表中:
INSERT
INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
SELECT ?, ? FROM DUAL
UNION ALL
SELECT ?, ? FROM DUAL;
如果 NVARCHAR 值都是 NULL 或都是非 NULL，则一切运行正常，我观察到恰好插入了 2 行。但是，如果我在单个 PreparedStatement 中混合使用 NULL 和非 NULL 值，我会立即收到一个 ORA-12704: 字符设置不匹配错误:
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)
这是重现问题的代码:
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));
}
}
}
奇怪的是，如果我明确地将我的参数转换为 NCHAR，上面的单元测试就可以顺利通过:
INSERT
INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
SELECT ?, TO_NCHAR(?) FROM DUAL
UNION ALL
SELECT ?, TO_NCHAR(?) FROM DUAL;
或切换到 INSERT ALL 语法:
INSERT ALL
INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
VALUES (?, ?)
INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
VALUES (?, ?)
SELECT * FROM DUAL;
但是原始代码有什么问题呢？
最佳答案
如果您可以拦截发送到数据库的实际查询，我想它看起来类似于:
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
在 Oracle 中，如果您这样做:
SELECT N'abc' FROM dual
UNION ALL
SELECT 'abc' FROM dual
你会得到错误:
ORA-12704: character set mismatch
来自 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.
所以回到你的工作方法:
1) 相同的数据类型(显式转换)
INSERT
INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
SELECT ?, TO_NCHAR(?) FROM DUAL
UNION ALL
SELECT ?, TO_NCHAR(?) FROM DUAL;
2) 两个“独立的”INSERTs:
INSERT ALL
INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
VALUES (?, ?)
INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
VALUES (?, ?)
SELECT * FROM DUAL;
3) “如果 NVARCHAR 值要么都是 NULL 要么都是非 NULL，一切运行正常，我观察到正好插入了 2 行”- 相同的数据类型，所以它工作正常
INSERT
INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
SELECT ?, ? FROM DUAL
UNION ALL
SELECT ?, ? FROM DUAL;
最后，如果存在 NULL 和 NOT NULL 值，则会产生错误。它清楚地表明映射无效。我认为这与:
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.
和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.
所以你的 could 看起来像:
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);
再想一想:Oracle 将空字符串视为与 NULL 相同，因此下面的代码也应该可以正常工作:
pstmt.setInt(1, 0);
pstmt.setNString(2, "NVARCHAR");
pstmt.setInt(3, 1);
pstmt.setNString(4, "");
关于java - ORA-12704 : character set mismatch when performing multi-row INSERT of nullable NVARCHAR's，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/45219916/