JDBC CallableStatement和Oracle SYS_REFCURSOR IN参数？ [英] JDBC CallableStatement and Oracle SYS_REFCURSOR IN parameters?

问题描述

基本上，我需要传递一个null（空的）指针游标作为存储过程的IN参数。

  / ** spring的PreparedStatementSetter＃setValues（...）* / 
 public void setValues（PreparedStatement ps）throws SQLException {
 CallableStatement cs =（CallableStatement）ps; 
 cs.setString（1，constant）; 
 //这是IN param 
 cs.setNull（2，OracleTypes.CURSOR）; 
 //这是OUT参数
 cs.registerOutParameter（3，OracleTypes.CURSOR）; 
} 
  

这会失败：

 造成的：java.sql.SQLException：不支持的功能：sqlType = -10 
 at oracle.jdbc.driver.DatabaseError.throwSqlException（DatabaseError.java:112） 
 at oracle.jdbc.driver.DatabaseError.throwSqlException（DatabaseError.java:146）
 at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical（OraclePreparedStatement.java:4399）
 at oracle.jdbc .driver.OraclePreparedStatement.setNullInternal（OraclePreparedStatement.java:4161）
 at oracle.jdbc.driver.OracleCallableStatement.setNull（OracleCallableStatement.java:4472）
  

我在这里失去了，尝试了几种不同的方式来做到这一点，但我找不到任何例子，不知道如何正确地做。 / p>

解决方案

我的java有点生锈，但你应该能够在PL / SQL块中初始化REF CURSOR变量： / p>

  String statement =DECLARE\\\
+ 
l_cursor SYS_REFCURSOR; \\\
+ 
 BEGIN\\\
+ 
your_procedure（？，l_cursor，？）; \\\
+ 
END; 
 CallableStatement cs = yourConnection.prepareCall（statement）; 
 //绑定第一个和最后一个参数
 cs.setString（1，constant）; 
 cs.registerOutParameter（2，OracleTypes.CURSOR）; 
  

Pretty much what it says in the title, how the hell is this supposed to be done?

Basically i need to pass a null (empty?) ref cursor as an IN parameter to a stored procedure.

/** spring's PreparedStatementSetter#setValues(...) */
public void setValues(PreparedStatement ps) throws SQLException {
    CallableStatement cs = (CallableStatement) ps;
    cs.setString(1,"constant");
    //this is the IN param
    cs.setNull(2, OracleTypes.CURSOR);
    //this is the OUT param
    cs.registerOutParameter(3, OracleTypes.CURSOR);
}

And this fails with:

caused by: java.sql.SQLException: Unsupported feature: sqlType=-10
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
    at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:4399)
    at oracle.jdbc.driver.OraclePreparedStatement.setNullInternal(OraclePreparedStatement.java:4161)
    at oracle.jdbc.driver.OracleCallableStatement.setNull(OracleCallableStatement.java:4472)

I'm at a loss here, tried several different ways to do this, but I couldn't find any examples about this and have no idea how to do it properly.

解决方案

My java is a bit rusty, but you should be able to initialize your REF CURSOR variable in a PL/SQL block:

String statement = "DECLARE\n" +
                       "l_cursor SYS_REFCURSOR;\n" +
                   "BEGIN\n" +
                       "your_procedure(?, l_cursor, ?);\n" +
                   "END;"
CallableStatement cs = yourConnection.prepareCall(statement);
// bind first and last parameter
cs.setString(1,"constant");
cs.registerOutParameter(2, OracleTypes.CURSOR);

这篇关于JDBC CallableStatement和Oracle SYS_REFCURSOR IN参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！