Search code examples
javaoracle-databasejdbcplsqlboolean

Oracle JDBC 23c driver throws ORA-06550 error when executing PL/SQL with setBoolean against a 23c Database

I am getting the following error message while executing PLSQL procedure with boolean type using Oracle 23c JDBC driver against 23c Database.

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.ja va: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

This is my code:

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) { }
}
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(); }

The issue occurs only when Oracle JDBC driver 23c is used against 23c database.(ojdbc8.jar and ojdbc11.jar) The issue does not occur with older JDBC driver and database release versions.

Solution

  • This has to do with the new BOOLEAN type introduced in the 23c Oracle Database. The new 23c driver sends the boolean value set through setBoolean as a BOOLEAN (as opposed to a NUMBER in previous versions) and hence you're getting this ORA-06550 error because the Database is expecting a VARCHAR (your parameters are all VARCHAR).

    You have 3 options:

    1. Change your PLSQL procedure and make the parameters be BOOLEAN instead of VARCHAR
    2. Or change your Java code to use setInt(1/0) instead of setBoolean(true/false)
    3. Or set the JDBC property oracle.jdbc.sendBooleanInPLSQL to false which is a compatibility flag used to make the driver revert to its old behavior. This property can be set on the DataSource or as a Java system property with -Doracle.jdbc.sendBooleanInPLSQL=false.

    If your PLSQL procedure truely deals with BOOLEAN parameters then option #1 is the best. Your code would look like this:

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

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

    Note the Types.BOOLEAN type used in registerOutParameter.

    Also note that option #3 is not recommended and should just be used as a temporary workaround because you will loose the benefits of the new BOOLEAN type. For example, indexes on a BOOLEAN column won't work.