Vert.x, Stored Proc calls, and SYS_REFCURSOR

[John Watzke]

Folks, I seem to be having trouble with the new stored proc call stuff and I'm not sure if I'm just doing it completely wrong or if there is some bug here.  I have a somewhat complicated DB function in Oracle that I need to call and I can't seem to manage to call it properly with Vert.x.  I've tried all kinds of variations of parameters/function call strings and no luck.  Is there anything obviously wrong with the below?

Here's what the function looks like in terms of input/output parameters:

create or replace FUNCTION  "RP_GET_KITTEN_RECORDS_2" (
   /* Input Arguments   */
   v_start_date IN TIMESTAMP DEFAULT NULL,
   v_end_date IN TIMESTAMP DEFAULT NULL,
   v_category IN VARCHAR2 DEFAULT NULL,
   v_rowcount IN NUMBER DEFAULT NULL,
   iv_kitten_id IN VARCHAR2 DEFAULT NULL,
   iv_store_id IN VARCHAR2 DEFAULT NULL,
   iv_breeder IN VARCHAR2 DEFAULT NULL,
   iv_username IN VARCHAR2 DEFAULT NULL,
   iv_keyword IN VARCHAR2 DEFAULT NULL,
   v_category_subtype IN VARCHAR2 DEFAULT NULL,
   /* Output Arguments   */
   v_err_msg OUT VARCHAR2,
   cv_1 IN OUT SYS_REFCURSOR
) RETURN NUMBER

And here's a snippet of my code trying to call it:

private static final String GET_KITTEN_ENTRIES = "{? = call kitten_rpt.rp_get_kitten_records_2(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}";

// setup JDBCClient and get a connection
// ....

JsonArray inputs = new JsonArray().addNull().add("2015-03-01").add("2016-03-01").add("Fluffy").add(2000).add("kitten123").addNull.add("Super Breeder").add("watzkej").addNull().addNull().addNull().addNull();
JsonArray outputs = new JsonArray().add("NUMERIC").addNull().addNull().addNull().addNull().addNull().addNull().addNull().addNull().addNull().addNull().add("VARCHAR").add("REF_CURSOR");
sqlConnection.callWithParams(GET_AUDIT_ENTRIES, inputs, outputs, query -> {
   // Do Stuff
}

When I put the REF_CURSOR type on, I get "java.sql.SQLException: Invalid column type: 2012".  When I try to set that REF_CURSOR output param to null (or leave it out since I don't need it), the function cannot be found because the number of parameters no longer matches.  The error is coming as a java.sql.SQLException but I can't tell exactly what the problem is.  This stored proc is from a legacy app which we are converting to vert.x so it seems to work just fine with using plain old Java JDBC.  In the plain JDBC code, we're using an Oracle.CURSOR type.  Of course, in vert.x, I have to stick with types available in the JDBCTypes enum since there doesn't appear to be another way to pass the type down.

Any help would be greatly appreciated!

[Paulo Lopes]

Can you get me an example of this proc? a full working example so i can try to reproduce and investigate?

There was a recent PR to fix INOUT params that might solve your issue: https://github.com/vert-x3/vertx-jdbc-client/pull/45

But you are saying that you need to specify a special type so i'm not sure if that will work. So if you can make a full test case then i can try to investigate...

Cheers,
Paulo

[John Watzke]

I'm not really well versed in creating stored procs and I'm just trying to use an existing one. I can't copy the actual stored proc (which isn't about kittens) to a forum. That pull request could possibly solve the problem.  It might be easier to try it out than make up a testcase. If that pull request doesn't work I can try to make up some kind of testcase for you.

To be clear, I don't think I really need to use an Oracle.CURSOR type although that's what the original Java code did.  I think they did this so they could get to some special Oracle specific functions or perhaps the dev didn't know they could just use a regular JDBC CURSOR type.  I don't plan to use the actual cursor from this call since vertx should pull the resultset for me but I do have to include it since it is a parameter of the function.

Thanks for your reply!