JDBC CallableStatement 和 Oracle SYS_REFCURSOR IN 参数？

发布于 2024-08-29 21:03:20 字数 1074 浏览 2 评论 0原文

和标题中说的差不多，到底该怎么做呢？

基本上我需要将空（空？）引用游标作为 IN 参数传递给存储过程。

/** 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);
}

这失败了：

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)

我在这里不知所措，尝试了几种不同的方法来做到这一点，但我找不到任何关于此的例子，也不知道如何正确地做到这一点。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

卷耳 2024-09-05 21:03:20

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

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);

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);

回复收藏 0 原文

~没有更多了~