I have a strange behaviour, which I could not identified to source of it.
Vertx: 4.5.3
Oracle-JDBC: 19.18.0.0
A really easy JDBC-Call into the database:
Pool pool = JDBCPool.pool(vertx,
// configure the connection
new JDBCConnectOptions()
.setJdbcUrl("jdbc:oracle:thin:@//localhost:1521/instance")
.setUser("tiger")
.setPassword("scott"),
new PoolOptions()
.setMaxSize(16)
.setName("pool-name")
);
String sql = "{call set_privileges(p_privilege_id => ?, p_user_id=> ?, p_priv_group => ?, p_sub_priv_group => ?)}";
pool
.preparedQuery(sql)
.execute(Tuple.of(
SqlOutParam.INOUT(null,"NUMERIC"), // p_privilege_id
1001, // p_user_id
1, // p_priv_group
null)) // p_sub_priv_group
.onFailure(e -> {
log.error(e);
})
.onSuccess(rows -> {
log.info(rows.rowCount());
});
All parameter are Integer, if the SQLOut-Parameter is set to null (as above), the JDBC-Driver raises an exception
2024-03-02 16:17:04,586 ERROR [vert.x-worker-thread-19] sg.ae.ag.co.se.im.OracleExceptionStack Oracle Fatal Error
java.sql.SQLException: Parameter Type Conflict
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2395) ~[ojdbc10-19.18.0.0.jar:19.18.0.0.0]
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3657) ~[ojdbc10-19.18.0.0.jar:19.18.0.0.0]
at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1358) ~[ojdbc10-19.18.0.0.jar:19.18.0.0.0]
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778) ~[ojdbc10-19.18.0.0.jar:19.18.0.0.0]
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4251) ~[ojdbc10-19.18.0.0.jar:19.18.0.0.0]
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081) ~[ojdbc10-19.18.0.0.jar:19.18.0.0.0]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-5.0.1.jar:?]
at com.zaxxer.hikari.pool.HikariProxyCallableStatement.execute(HikariProxyCallableStatement.java) ~[HikariCP-5.0.1.jar:?]
at io.vertx.jdbcclient.impl.actions.JDBCPreparedQuery.execute(JDBCPreparedQuery.java:64) ~[vertx-jdbc-client-4.5.3.jar:4.5.3]
at io.vertx.jdbcclient.impl.actions.JDBCPreparedQuery.execute(JDBCPreparedQuery.java:44) ~[vertx-jdbc-client-4.5.3.jar:4.5.3]
at io.vertx.ext.jdbc.impl.JDBCConnectionImpl.lambda$schedule$3(JDBCConnectionImpl.java:226) ~[vertx-jdbc-client-4.5.3.jar:4.5.3]
at io.vertx.core.impl.ContextImpl.lambda$executeBlocking$1(ContextImpl.java:190) ~[vertx-core-4.5.3.jar:4.5.3]
It can be workaround, to set in any case of null "0", and convert "0" back to NULL within the procedure, but is there a better/correct way to pass "NULL" in an INOUT-Variable?
Update: I found an hint, to use NullValue.xxxxxx in case of "null",
SqlOutParam.INOUT(NullValue.Integer,"NUMERIC")
but even this does not solve it.
Can be workaround with
Tuple.of(SqlOutParam.INOUT(NullValue.String,JDBCType.NVARCHAR))