JDBC CallableStatement and Oracle SYS_REFCURSOR IN options?
Quite a lot of what the title says, how the hell is this supposed to be done?
Basically I need to pass a ref cursor (empty?) As an IN parameter to a stored procedure.
/** spring 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 it fails:
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 of this and had no idea how to do it properly.
+2
a source to share
1 answer
My java is a little rusty, but you should be able to initialize the 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
a source to share