Announcement Announcement Module
Collapse
No announcement yet.
StoredProcedureItemReader - Error: Invalid column index Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • StoredProcedureItemReader - Error: Invalid column index

    I am using the StoredProcedureItemReader with an sp that just has one out param that is a ref cursor, following the reference documentation I got to this configuration:

    Code:
    	<beans:bean id="orderReader" class="org.springframework.batch.item.database.StoredProcedureItemReader">
    	    <beans:property name="dataSource" ref="dataSource"/>
    	    <beans:property name="procedureName" value="pkg_claim_comp.sp_get_orders_items"/>
    	    <beans:property name="refCursorPosition" value="1"/>
    	    <beans:property name="rowMapper" ref="orderLineMapper" />
    	</beans:bean>
    The documentation says that this configuration is sufficient and will work for an stored procedure that has only one (output) parameter. But after executing the job I get this error message:

    Code:
    Executing stored procedure; invalid ResultSet access for SQL [{call PKG_CLAIM_COMP.SP_GET_ORDERS_ITEMS()}]; nested exception is java.sql.SQLException: Invalid column index
    As you can see the call statement is wrong it should be something like this: {call PKG_CLAIM_COMP.SP_GET_ORDERS_ITEMS(?)}

    Is the documentation wrong?

    Thanks.

  • #2
    I got it working by defining the output parameter, in the documentation they say that you do not need this unless you have input parameters, but this seems to be wrong, here is the configuration that worked for me:

    Code:
    	<beans:bean id="orderReader" class="org.springframework.batch.item.database.StoredProcedureItemReader">
    	    <beans:property name="dataSource" ref="dataSource"/>
    	    <beans:property name="procedureName" value="pkg_claim_comp.sp_get_orders_items"/>
            <beans:property name="parameters">
    	        <beans:list>
    	            <beans:bean class="org.springframework.jdbc.core.SqlOutParameter">
    	                <beans:constructor-arg index="0" value="c_orders"/>
    	                <beans:constructor-arg index="1">
    	                    <util:constant static-field="oracle.jdbc.OracleTypes.CURSOR"/>
    	                </beans:constructor-arg>
    	            </beans:bean>            
    	        </beans:list>
        	    </beans:property>
    	    <beans:property name="refCursorPosition" value="1"/>
    	    <beans:property name="rowMapper" ref="orderLineMapper" />
    	</beans:bean>
    Hope it helps

    Comment


    • #3
      Can you please tell me how do you get the resultset out of the refcursor(that is returned as OUT parameters) when using storedprocedureitemreader?

      My original post.
      http://forum.springsource.org/showth...473#post448473

      Comment

      Working...
      X