StoredProcedureItemReader and Oracle SYS_REFCURSOR as an IN parameter?

[deebo]

I've no idea how to get this to work, have spent hours looking around to no avail.

I'm trying to call a Oracle stored procedure with an optional IN cursor parameter, with little to no success...

Params:

    <bean id="myReader" parent="baseReader" p:procedureName="myProcedure">
        <property name="parameters">
        <list>
            <bean class="org.springframework.jdbc.core.SqlParameter">
                <constructor-arg index="0">
                    <util:constant static-field="java.sql.Types.VARCHAR"/>
                </constructor-arg>
            </bean>
            <bean class="org.springframework.jdbc.core.SqlParameter">
                <constructor-arg index="0">
                    <util:constant static-field="oracle.jdbc.OracleTypes.CURSOR"/>
                </constructor-arg>
            </bean>
            <bean class="org.springframework.jdbc.core.SqlOutParameter">
                <constructor-arg index="0">
                    <util:constant static-field="oracle.jdbc.OracleTypes.CURSOR"/>
                </constructor-arg>
            </bean>
        </list>

Setter:

    public void setValues(PreparedStatement ps) throws SQLException {
        CallableStatement cs = (CallableStatement) ps;
        //constant
        cs.setString(1, "001");
        //null cursor
        cs.setNull(2, OracleTypes.CURSOR);
        //result cursor
        cs.registerOutParameter(3, OracleTypes.CURSOR);
    }

and this results in

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)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.setNull(DelegatingPreparedStatement.java:105)
        at my.package.MyParameterSetter.setValues(MyParameterSetter.java:xyz)

Anyone know how this is done properly?

[wriggle]

Not sure if it is the same problem as mentioned here http://forum.springsource.org/showthread.php?p=295972.

Could you try using the StoredProcedureItemReader patch attached in this thread and tell us if it works ?

Thanks

[deebo]

My problem was using the wrong version of the Oracle JDBC driver

version 9.0.2.0 did not support IN parameter Cursors at all

I upgraded to 9.2.8.0 and everything works fine.