CODEWITHSUNDEEP
javajdbcvert.x
Mhammad Sellam
Mhammad Sellam

Reputation: 160

Using Vertx and jdbc to get result of Oracle stored procedure ref_cursor

I am trying to read an Oracle stored procedure returning a ref_cursor using vertx3. The same procedure is working if I edit it to return clob and use JDBCType.CLOB but for some reason I have to use ref_cursor. Can someone please help me?

JDBCClient client = JDBCClient.createShared(vertx, new JsonObject()
                .put("url", "jdbc:oracle:thin:@localhost:8787:TEST")
                .put("driver_class", "oracle.jdbc.OracleDriver")
                .put("user", "user")
                .put("password", "****"));

 client.getConnection( connection -> {
      if (connection.succeeded()) {
          SQLConnection con = connection.result();

         JsonObject params = new JsonObject()
                    .put("query", "{ call ? :=  package.procedure(?) }")
                    .put("paramsIn", new JsonArray().addNull().add(89))
                    .put("paramsOut", new JsonArray().add(JDBCType.REF_CURSOR));

          con.callWithParams(params.getString("query"), params.getJsonArray("paramsIn"),  params.getJsonArray("paramsOut"), query -> {
               if(query.succeeded()){
                     ResultSet rs = query.result();
                     System.out.println(rs.toJson().toString())
                 }else{
                  System.out.println(req.body() + query.cause().toString());
                 }
                });
             } else {  System.out.println(connection.cause().toString())                 
             }
            });

and I get the error:

 java.sql.SQLException: Type de colonne non valide: 2012

Upvotes: 0

Views: 1526

Answers (1)

Paulo Lopes
Paulo Lopes

Reputation: 5801

Cursors cannot be converted to JSON so they are not supported by Vert.x JDBC client. However it seems that your driver does not recognize the REF_CURSOR type either. I think it got added in JDK8 and probably your driver was compiled for a previous version?

Upvotes: 1

Related Questions