Search code examples
vert.x

Vertx Oracle Null-Value on SQL-INOUT-Parameter

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.

Solution

  • Can be workaround with

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