Announcement Announcement Module
Collapse
No announcement yet.
StoredProcedureItemReader and Oracle SYS_REFCURSOR as an IN parameter? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • StoredProcedureItemReader and Oracle SYS_REFCURSOR as an IN parameter?

    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:
    Code:
        <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:
    Code:
        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
    Code:
    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?

  • #2
    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

    Comment


    • #3
      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.

      Comment

      Working...
      X