ORA-12704: несоответствие набора символов при выполнении многострочной вставки обнуляемых NVARCHAR
Рассмотрим следующую таблицу, где один из столбцов имеет тип nullable 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 вставленных строки. Если, однако, я смешиваю NULL и неNULL значения в пределах одного PreparedStatementЯ сразу получаю ORA-12704: character set mismatch ошибка:
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;
Но что не так с оригинальным кодом?
3 ответа
Если бы вы могли перехватить реальный запрос, который отправляется в БД, я думаю, он выглядит примерно так:
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: несоответствие набора символов
От UNION ALL doc:
Если запрос компонента выбирает символьные данные, тип возвращаемых значений определяется следующим образом:
Если оба запроса выбирают значения типа данных CHAR одинаковой длины, то возвращаемые значения имеют тип данных CHAR этой длины. Если запросы выбирают значения CHAR с различной длиной, то возвращаемым значением является VARCHAR2 с длиной большего значения CHAR.
Если один или оба запроса выбирают значения типа данных VARCHAR2, то возвращаемые значения имеют тип данных 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значение будет генерировать ошибку. Это ясно указывает на то, что отображение недействительно. Я считаю, что это связано с:
Допустимые сопоставления типов данных SQL-JDBC:
┌────────────────────────┬──────────────────────────────────────────┐ │ These SQL data types: │ Can be materialized as these Java types: │ ├────────────────────────┼──────────────────────────────────────────┤ │ NVARCHAR2 │ no (see Note) │ └────────────────────────┴──────────────────────────────────────────┘Примечание. Типы NCHAR и NVARCHAR2 поддерживаются косвенно. Не существует соответствующего типа java.sql.Types, но если ваше приложение вызывает formOfUse (NCHAR), то к этим типам можно получить доступ.
И NCHAR, NVARCHAR2, NCLOB и свойство defaultNChar в JDK 1.5:
По умолчанию интерфейс oracle.jdbc.OraclePreparedStatement обрабатывает тип данных всех столбцов так же, как они закодированы в наборе символов базы данных. Однако, начиная с Oracle Database 10g, если вы установите значение системного свойства oracle.jdbc.defaultNChar в true, то JDBC будет обрабатывать все символьные столбцы как национальные.
Значением по умолчанию defaultNChar является false.Если значение defaultNChar равно false, вы должны вызвать метод setFormOfUse(, OraclePreparedStatement.FORM_NCHAR) для тех столбцов, которые конкретно нуждаются в символах на национальном языке.
Таким образом, ваш может выглядеть так:
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, "");
Можете ли вы попробовать использовать вместо этого следующий sql:
SELECT ?, cast(? as nvarchar2(32)) FROM DUAL
UNION ALL
SELECT ?, cast(? as nvarchar2(32)) FROM DUAL;
Я думаю, что ваша ошибка, потому что по умолчанию null имеет тип varchar2, и существует несоответствие типов в объединении всех частей вашего sql. Кстати, чтобы проверить, что вы можете запустить этот SQL без вставки части и посмотреть, если ошибка по-прежнему существует или нет.
Я рекомендую вам три чека.
Сначала измените эту часть:
pstmt.setInt(1, 0);
pstmt.setNString(2, "NVARCHAR");
pstmt.setInt(3, 1);
pstmt.setNull(4, Types.NVARCHAR);
к этому:
pstmt.setInt(1, 0);
pstmt.setString(2, "NVARCHAR");
pstmt.setInt(3, 1);
pstmt.setString(4, null);
(Я думаю, что это не ваша проблема. Это только рекомендация, потому что это может решить проблему с набором символов базы данных)
Во-вторых, проверьте ваш набор символов пула соединений: предпочтите установить "UTF-8". что-то вроде этого spring.datasource.connectionProperties=useUnicode=true;characterEncoding=utf-8;
или, может быть, вы установили это на сервере приложений, или вы можете обработать это в коде.
В-третьих, вы должны проверить оператор вставки с помощью инструментов sql, таких как plsql developer или..., и протестировать этот оператор напрямую:
INSERT INTO CHARACTER_SET_MISMATCH_TEST (ID, VALUE)
SELECT 1, 'test' FROM DUAL
UNION ALL
SELECT 2, null FROM DUAL;
или даже это:
SELECT 1 aa, 'test' bb FROM DUAL
UNION ALL
SELECT 2 aa, null bb FROM DUAL;
Если вы получили ошибку снова. это потому, что ваш набор символов базы данных и не связан с вашим кодом.
Я надеюсь, что это поможет.