CODEWITHSUNDEEP
oracle-databasejdbcvert.x
Chlorek
Chlorek

Reputation: 158

Vert.x and Oracle Database - how do I call function that returns table of integers?

I am trying to call stored function that returns table of integers. The query I am trying to use is: { call ? := SOME_PACKAGE.get_some_ids(?, ?, ?, ?, ?, ?) }

To call function that looks like this: FUNCTION get_some_ids(one IN INTEGER, two IN INTEGER, three IN INTEGER, four IN INTEGER, five IN INTEGER, six OUT INTEGER) RETURN int_tab;

And I prepare this call as such:

conn.callWithParams(QUERY_GET_CONTRACTS,
    new JsonArray()
        .addNull()
        .add(10).add(10).add(10).add(10).add(10),
    new JsonArray()
        .add(OracleTypes.ARRAY)
        .addNull().addNull().addNull().addNull().addNull()
        .add("INTEGER"),

Such approach works fine when I called function that returns ref cursor (of course I used OracleTypes.CURSOR instead). The code results in following exception: java.sql.SQLException: Fail to construct descriptor: empty Object name

Let's assume I can't change anything in database. I've tried some tricks with using 'begin end' constructs but failed miserably. Any clues will be greatly appreciated, thank you!

Upvotes: 1

Views: 415

Answers (1)

Chlorek
Chlorek

Reputation: 158

As far as I'm concerned it's not possible with out of box vertx APIs. To achieve what I needed I had to create new classes like JDBCClientImpl and JDBCConnectionImpl. Client's getConnection() method spawns my custom JDBCConnectionImpl and this connection exposes unwrap() method to the public, so I can do usual JDBC. In the end I can use it like this:

CallableStatement callable = ((OJDBCConnectionImpl)conn).unwrap().prepareCall("my call");
callable.registerOutParameter(1, OracleTypes.ARRAY, "INT_TAB");

It is not perfect but it works if you have no choice. I think they should provide a way for registering parameteres the way we manually can, but there's no comfortable way to do this through JSON-dependant API.

Upvotes: 1

Related Questions