CODEWITHSUNDEEP
javaoracle-databasejdbi
Mewster
Mewster

Reputation: 1063

JDBI + oracle stored procedure + out parameter as object

I'm trying to call, through JDBI, a Oracle stored procedure with a input parameter and a custom output parameter.

PROCEDURE customProcedure(in_val IN NUMBER, out_val OUT CUSTOM_OBJECT)

(where CUSTOM_OBJECT) is defined as

CREATE TYPE CUSTOM_OBJECT AS OBJECT
(
    a NUMBER,
    b VARCHAR,
    ....
)

The code is

    @SqlCall("call customProcedure(:input,:result)")
    @OutParameter(name="result", sqlType=Types.STRUCT)
    OutParameters callProcedure(@Bind("input") Long input);

but I get only a java.sql.SQLException: Invalid argument(s) in call during the out parameter binding phase.

Debugging the OracleCallableStatement I arrived in a registerOutParameterInternal method, where in a switch case with my specified type (STRUCT) if a variable is null, an exception is thrown, but this variable arrives internally from

this.registerOutParameterInternal(var1, var2, var3, var4, **(String)null**);

I tried different out types, but I get either a java.sql.SQLException: Invalid column type: [int value from the chosen sqlType], or a type mismatch from the stored procedure (oracle.jdbc.OracleDatabaseException: ORA-06553: PLS-306: wrong number or types of arguments in call to 'customProcedure') .

I tried also to call the query manually (using the jdbi.handle.createCall("...").registerOutParameter(...)) without differences.

How can I call a stored procedure with a custom output type?

Thanks in advance

Upvotes: -1

Views: 283

Answers (1)

Petr Barandovski
Petr Barandovski

Reputation: 168

I think, that now it is not possible. There is an issue: https://github.com/jdbi/jdbi/issues/2556 about It.

Try this workaround:

    Connection connection = poolDataSource.getConnection();
    CallableStatement cs = connection.prepareCall("{call customprocedure(?,?)}");
    cs.setLong(1,21l);
    cs.registerOutParameter(2, Types.STRUCT, "CUSTOM_OBJECT");
    cs.execute();
    Struct object = (Struct) cs.getObject(2);
    Object[] attributes = object.getAttributes();
    BigDecimal attribute = (BigDecimal) attributes[0];
    String attribute1 = (String) attributes[1];
    System.out.println(attribute);
    System.out.println(attribute1);

Upvotes: 0

Related Questions