CallableStatement with Named binding leads to PLS-00306: wrong number or types of arguments

Question

We need to update oracle driver in our java8 project. We changed it from ojdbc6 to ojdbc8 19.3.0.0 whereupon we faced such exception

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

executing following code:

try (CallableStatement cs = con.prepareCall("{call :result := CORE.PKG.makeWork(arg1=>:arg1, arg2=>:arg2)}")) {
    cs.setString("arg1", javaArg1);
    cs.setInt("arg2", javaArg2);
    cs.registerOutParameter("result", Types.ARRAY, CommonTypeNameConstant.T_TABLE_OF_NUMBERS_TYPE);
    cs.execute();
    Array result = (Array) cs.getObject("result");

It only happens with functions. I mean, code would work if CORE.PKG.makeWork was a procedure:

try (CallableStatement cs = con.prepareCall("{call CORE.PKG.makeWork(arg1=>:arg1, arg2=>:arg2)}")) {
    cs.setString("arg1", javaArg1);
    cs.setInt("arg2", javaArg2);
    cs.execute();

The problem can be resolved via migrating oracle scripts from Named binding to Ordinal binding:

try (CallableStatement cs = con.prepareCall("{? = call CORE.PKG.makeWork(arg1=>?, arg2=>?)}")) {
    cs.registerOutParameter(1, Types.ARRAY, CommonTypeNameConstant.T_TABLE_OF_NUMBERS_TYPE);
    cs.setObject(2, javaArg1);
    cs.setObject(3, javaArg2);
    cs.execute();
    Array result = (Array) cs.getObject(1);

But we want to keep Named binding in out project because Ordinal binding tend developers to make order mistakes while listing parameters + we have quite a few scripts to rewrite.

Why wouldn't Named binding of output params work with new version of driver?

Answer 1

Judging by the docs you can still use named binds in sql, but have to set values to them by position:

try (CallableStatement cs = con.prepareCall("{call CORE.PKG.makeWork(arg1=>:arg1, arg2=>:arg2)}")) {
    cs.setString(1, javaArg1);
    cs.setInt(2, javaArg2);
    cs.execute();

Or you can use oracle.jdbc.OracleCallableStatement to set values to binds by name, but have to replace all ":params" with "?":

The setXXX(String,...) and registerOutParameter(String,...) methods can be used only if all binds are procedure or function parameters only. The statement can contain no other binds and the parameter binds must be indicated with a question mark (?) and not :XX

try (CallableStatement cs = con.prepareCall("{call CORE.PKG.makeWork(arg1=>?, arg2=>?)}")) {
    cs.setString("arg1", javaArg1);
    cs.setInt("arg2", javaArg2);
    cs.execute();

Or you can use NamedParameterJdbcTemplate from Spring JDBC