Call Function with INOUT-Null-Value

[nikodemusP]

Version:

Vert-X: 4.5.4
Oracle-JDBC: 19.18.0.0

Issue:

In case of database-procedure and the parameter is defined as an INOUT and the input-value is NULL, the wrong type is passed to JDBC-Driver. It makes no different if the tuble-parameter is set to

Tuple.of(SqlOutParam.INOUT(NullValue.Integer,JDBCType.INTEGER)
Tuple.of(SqlOutParam.INOUT(NullValue.Double,JDBCType.INTEGER)
Tuple.of(SqlOutParam.INOUT(NullValue.Double,JDBCType.DOUBLE)
Tuple.of(SqlOutParam.INOUT(null,JDBCType.INTEGER)

The JDBC-Driver everytime response with Parameter type conflict. The Input-Part is set to VARCHAR, the Output to TINYINT, regardless, what is defined within the Tuble-Parameter.

HTTP server started on port 8888
java.sql.SQLException: ORA-17012: Parameter type conflict (input type=9 output type=6)
https://docs.oracle.com/error-help/db/ora-17012/
        at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2486)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3739)
        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)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
        at com.zaxxer.hikari.pool.HikariProxyCallableStatement.execute(HikariProxyCallableStatement.java)
        at io.vertx.jdbcclient.impl.actions.JDBCPreparedQuery.execute(JDBCPreparedQuery.java:64)
        at io.vertx.jdbcclient.impl.actions.JDBCPreparedQuery.execute(JDBCPreparedQuery.java:44)
        at io.vertx.ext.jdbc.impl.JDBCConnectionImpl.lambda$schedule$3(JDBCConnectionImpl.java:226)
        at io.vertx.core.impl.ContextImpl.lambda$executeBlocking$1(ContextImpl.java:190)
        at io.vertx.core.impl.ContextInternal.dispatch(ContextInternal.java:276)
        at io.vertx.core.impl.ContextImpl.lambda$internalExecuteBlocking$2(ContextImpl.java:209)
        at io.vertx.core.impl.TaskQueue.run(TaskQueue.java:76)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
        at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
        at java.base/java.lang.Thread.run(Thread.java:833)
In: NVARCHAR
Out: TINYINT
java.sql.SQLException: ORA-17012: Parameter type conflict (input type=9 output type=6)
https://docs.oracle.com/error-help/db/ora-17012/
        at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2486)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3739)
        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)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
        at com.zaxxer.hikari.pool.HikariProxyCallableStatement.execute(HikariProxyCallableStatement.java)
        at io.vertx.jdbcclient.impl.actions.JDBCPreparedQuery.execute(JDBCPreparedQuery.java:64)
        at io.vertx.jdbcclient.impl.actions.JDBCPreparedQuery.execute(JDBCPreparedQuery.java:44)
        at io.vertx.ext.jdbc.impl.JDBCConnectionImpl.lambda$schedule$3(JDBCConnectionImpl.java:226)
        at io.vertx.core.impl.ContextImpl.lambda$executeBlocking$1(ContextImpl.java:190)
        at io.vertx.core.impl.ContextInternal.dispatch(ContextInternal.java:276)
        at io.vertx.core.impl.ContextImpl.lambda$internalExecuteBlocking$2(ContextImpl.java:209)
        at io.vertx.core.impl.TaskQueue.run(TaskQueue.java:76)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
        at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
        at java.base/java.lang.Thread.run(Thread.java:833)

Do you have a reproducer?

I pushed a rep-case to Github:

Rep-Case-NullValue

The test.sql contains the package, just with one INOUT-Parameter.

[nikodemusP]

Additional information, the issue can be workaround, with

.execute(Tuple.of(SqlOutParam.INOUT(NullValue.String,JDBCType.NVARCHAR)))

[tsegismont]

Possibly related to #1246