ORA-01461: 仅能绑定要插入 LONG 列的 LONG 值。ojdbc8版本23.2.0.0驱动BUG【已解决】

问题描述

JDK8使用ojdbc8驱动操作oracle11g数据库，使用JDBC复用 PreparedStatement 对象执行Insert操作时，报错java.sql.SQLException: ORA-01461: 仅能绑定要插入 LONG 列的 LONG 值。，经测试发现，是预编译对象某个占位符号被赋值为NULL后，再次尝试赋值为2个汉字，执行时就会报错：

Caused by: java.sql.SQLException: ORA-01461: 仅能绑定要插入 LONG 列的 LONG 值at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:702)at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:608)at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1248)at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:1041)at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:443)at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:518)at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:251)at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1181)at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1571)at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1345)at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3728)at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3905)at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3880)at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:993)at com.p6spy.engine.wrapper.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:94)at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeUpdate(DruidPooledPreparedStatement.java:241)

问题复现

测试表DDL

CREATE TABLE "TEST_TABLE" ("TEST_COLUMN" VARCHAR2(255 BYTE)
)

java代码

   @Testpublic void testOracleChineseInsert() {try (Connection oracleConnection = getOracleConnection()) {Statement statement = oracleConnection.createStatement();statement.execute("truncate table TEST_TABLE");PreparedStatement preparedStatement = oracleConnection.prepareStatement("INSERT INTO TEST_TABLE VALUES (?)");List<String> list = new ArrayList<>();list.add("你好");list.add(null);list.add("你好");for (String data : list) {try {preparedStatement.setString(1, data);preparedStatement.executeUpdate();} catch (SQLException e) {throw new RuntimeException(e);}}} catch (SQLException e) {throw new RuntimeException(e);}}

ORACLE数据库字符集

-- 查询字符集
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
-- 查询结果
NLS_CHARACTERSET	ZHS16GBK
NLS_NCHAR_CHARACTERSET	AL16UTF16

依赖版本

<dependency><groupId>com.oracle.database.jdbc</groupId><artifactId>ojdbc8</artifactId><version>23.2.0.0</version>
</dependency>

执行结果

前两条记录能新增成功，第三条记录报错 ORA-01461: 仅能绑定要插入 LONG 列的 LONG 值

解决办法

更换驱动版本后发现该BUG已被修复，更新驱动版本（推荐）

<dependency><groupId>com.oracle.database.jdbc</groupId><artifactId>ojdbc8</artifactId><version>23.6.0.24.10</version>
</dependency>

或者代码中避免复用预编译对象，每个语句新建一个预编译对象（不推荐）

   @Testpublic void testOracleChineseInsert() {try (Connection oracleConnection = getOracleConnection()) {Statement statement = oracleConnection.createStatement();statement.execute("truncate table TEST_TABLE");List<String> list = new ArrayList<>();list.add("你好");list.add(null);list.add("你好");for (String data : list) {try {PreparedStatement preparedStatement = oracleConnection.prepareStatement("INSERT INTO TEST_TABLE VALUES (?)");preparedStatement.setString(1, data);preparedStatement.executeUpdate();} catch (SQLException e) {throw new RuntimeException(e);}}} catch (SQLException e) {throw new RuntimeException(e);}}