Oracle db - calling function returning table of integers

109 views
Skip to first unread message

Chlorek Chlorine

unread,
Sep 14, 2018, 3:38:37 AM9/14/18
to vert.x

Hello, 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 to 'begin end' constructs but failed miserably. Any clues will be greatly appreciated, thank you!

Chlorek Chlorine

unread,
Sep 21, 2018, 4:22:49 AM9/21/18
to vert.x
So after awhile without answer I had to move on, read vertx-jdbc-client source and found out there is no way to cause call such as
callable.registerOutParameter(i, OracleTypes.ARRAY, "INT_TAB");
So I made my own classes implementing JDBCClient (like JDBCClientImpl) and SQLConnection (like JDBCConnectionImpl), with only changes that client spawns connections as my custom class, finally my connection class exposes unwrap() method so I have access to real java.sql.Connection, so I could call registerOutParameter() on my own. It works but I'm worried it will mess up something like pooling or something else. All I do with this connection is:

CallableStatement callable = ((OJDBCConnectionImpl)conn).unwrap().prepareCall(QUERY_GET_IDS);
int c = 0;
callable
.registerOutParameter(++c, OracleTypes.ARRAY, "INT_TAB");
callable
.setObject(++c, 855456);
callable
.setObject(++c, 0);
callable
.setObject(++c, -1);
callable
.setObject(++c, null);
callable
.setObject(++c, 50);
callable
.registerOutParameter(++c, OracleTypes.INTEGER);
callable
.execute();
conn
.close();
 What I want to know is if it will break something and if there is any better alternative available right now or in the nearest release?

Reply all
Reply to author
Forward
0 new messages