Oracle 23c JDBC调用PL/SQL时setBoolean触发ORA-06550错误

问题现象 #

使用Oracle 23c JDBC驱动（ojdbc8.jar或ojdbc11.jar）连接23c数据库执行PL/SQL存储过程时，触发以下错误：

java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TEMP_SENSOR'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TEMP_SENSOR'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

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:1330)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:1109)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:455)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:229)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1408)
at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1958)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1594)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3754)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4202)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4206)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1015)
Caused by: Error : 6550, Position : 6, SQL = BEGIN 
TEMP_SENSOR(:1,:2,:3) ; END;, Original SQL = { call 
TEMP_SENSOR(?,?,?) }, Error Message = ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TEMP_SENSOR'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TEMP_SENSOR'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:710)
... 16 more

该问题仅在Oracle 23c JDBC驱动搭配23c数据库时出现，旧版本驱动与数据库组合无此问题。

相关代码 #

存储过程创建代码 #

try(Statement stmt = conn.createStatement()) {
  try { stmt.execute("CREATE OR REPLACE PROCEDURE TEMP_SENSOR" +
          " (switch1 IN VARCHAR, switch2 OUT VARCHAR, switch3 IN OUT VARCHAR) IS" +
          "  BEGIN " +
          ...
          "  END;"); } catch (SQLException sqlex) { }
}

JDBC调用代码 #

try ( CallableStatement cstmt = conn.prepareCall("{ call TEMP_SENSOR(?,?,?) }"); ) {
  cstmt.setBoolean(1, false);
  cstmt.setBoolean(3, true);
  cstmt.registerOutParameter(2, Types.BIT);
  cstmt.registerOutParameter(3, Types.BIT);
  cstmt.execute();
} catch (SQLException sqlex) {sqlex.printStackTrace(); }

问题原因 #

Oracle 23c JDBC驱动对参数类型的匹配校验更加严格，旧版本驱动会隐式转换布尔类型与VARCHAR类型，但23c版本收紧了这一规则。存储过程定义的参数类型为VARCHAR，但Java代码中使用了setBoolean（传入布尔值）和Types.BIT（注册输出参数），导致类型不匹配触发PLS-00306错误。

解决方案 #

方案1：对齐存储过程的VARCHAR类型修改Java代码 #

将Java代码中的参数处理改为对应VARCHAR类型，传入字符串格式的布尔值，读取输出参数时再转换为布尔类型：

try ( CallableStatement cstmt = conn.prepareCall("{ call TEMP_SENSOR(?,?,?) }"); ) {
  // IN参数：将布尔值转为字符串传入
  cstmt.setString(1, Boolean.FALSE.toString());
  // OUT参数：注册为VARCHAR类型
  cstmt.registerOutParameter(2, Types.VARCHAR);
  // IN OUT参数：先设置字符串格式的布尔值，再注册输出类型
  cstmt.setString(3, Boolean.TRUE.toString());
  cstmt.registerOutParameter(3, Types.VARCHAR);
  
  cstmt.execute();
  
  // 读取输出参数并转换为布尔值
  String switch2Str = cstmt.getString(2);
  String switch3Str = cstmt.getString(3);
  boolean switch2 = Boolean.parseBoolean(switch2Str);
  boolean switch3 = Boolean.parseBoolean(switch3Str);
} catch (SQLException sqlex) {sqlex.printStackTrace(); }

方案2：修改存储过程参数类型为BOOLEAN #

将存储过程的参数类型改为PL/SQL的BOOLEAN类型，对齐Java的布尔类型：

CREATE OR REPLACE PROCEDURE TEMP_SENSOR
(switch1 IN BOOLEAN, switch2 OUT BOOLEAN, switch3 IN OUT BOOLEAN) IS
  BEGIN 
    ...
  END;

对应的Java代码可以使用OracleTypes.BOOLEAN（更准确）或Types.BIT来处理：

import oracle.jdbc.OracleTypes;

try ( CallableStatement cstmt = conn.prepareCall("{ call TEMP_SENSOR(?,?,?) }"); ) {
  cstmt.setBoolean(1, false);
  cstmt.registerOutParameter(2, OracleTypes.BOOLEAN);
  cstmt.setBoolean(3, true);
  cstmt.registerOutParameter(3, OracleTypes.BOOLEAN);
  
  cstmt.execute();
  
  boolean switch2 = cstmt.getBoolean(2);
  boolean switch3 = cstmt.getBoolean(3);
} catch (SQLException sqlex) {sqlex.printStackTrace(); }

内容的提问来源于stack exchange，提问作者Saurabh Verma