Fetching Data from Oracle using River

code_blue · March 22, 2016, 2:08pm

We are using River to fetch data from Oracle DB. In Oracle we have to use SYS_REFCURSOR inside a Stored Proc to return some result-set using SELECT query.

Does River support fetching data using SYS_REFCURSOR

jprante · March 22, 2016, 2:31pm

If you can use it from JDBC in a CallableStatement, yes.
Do not use river any more, it is no longer supported. Use JDBC importer or other supported tools.

code_blue · March 22, 2016, 2:54pm

Thanks jprante for your response.
Yes we will set the "callable" : true attribute.

How do we define the ref cursor inside register attribute?
Any documentation link or refrence will also help.

jprante · March 22, 2016, 3:30pm

At the moment, the type is restricted to JDBC types, but I can change that

github.com

jprante/elasticsearch-jdbc/blob/master/src/main/java/org/xbib/elasticsearch/jdbc/strategy/standard/StandardSource.java#L931


      
              for (Map.Entry<String, Object> me : values.entrySet()) {
                  // { "key" : { "pos": n, "type" : "VARCHAR", "field" : "fieldname" }, ... }
                  Map<String, Object> m = (Map<String, Object>) me.getValue();
                  Object o = m.get("pos");
                  if (o != null) {
                      Integer n = o instanceof Integer ? (Integer) o : Integer.parseInt(o.toString());
                      o =  m.get("type");
                      String type = o instanceof String ? (String) o: o.toString();
                      if (type != null) {
                          logger.debug("registerOutParameter: n={} type={}", n, toJDBCType(type));
                          try {
                              statement.registerOutParameter(n, toJDBCType(type));
                          } catch (Throwable t) {
                              logger.warn("can't register out parameter " + n + " of type " + type);
                          }
                      }
                  }
              }
              return this;
          }

I think non-standard Oracle cursor is of type -10, but I'm not sure. If you can show how you want to use the callable statements, I can maybe more helpful.