Announcement Announcement Module
Collapse
No announcement yet.
Using RefCursor on StoredProcedureItemReader Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using RefCursor on StoredProcedureItemReader

    I have a stored procedure that returns the refcursor. StoredprocedureitemReader should get the list of values 'PQ' and level, id from the resultset and put it in a flat file.

    [CODE]
    CREATE OR REPLACE PROCEDURE SER_DTLS(bus_date in varchar2,l_cursor out SYS_REFCURSOR,status out varchar2) AS
    BEGIN
    Status := 'SUCCESS';
    OPEN l_cursor FOR
    select 'PQ', ct.level, ct.id
    from table1 ct,
    where
    ct.DT <= to_date(bus_date ,'DD-Mon-YYYY')
    AND ct.DT >= to_date(bus_date ,'DD-Mon-YYYY')
    EXCEPTION
    WHEN OTHERS THEN Status := SQLCODE||SUBSTR(SQLERRM, 1, 100);
    END P_GET_SERVICE_DTLS;


    <bean id="pqDao" class="org.springframework.batch.item.database.Sto redProcedureItemReader">
    <property name="dataSource" ref="dataSource"/>
    <property name="procedureName" value="SER_DTLS"/>
    <property name="fetchSize" value="500"/>
    <property name="parameters">
    <list>
    <bean class="org.springframework.jdbc.core.SqlParameter" >
    <constructor-arg index="0" value="busDay"/>
    <constructor-arg index="1">
    <util:constant static-field="java.sql.Types.VARCHAR"/>
    </constructor-arg>
    </bean>
    <bean class="org.springframework.jdbc.core.SqlOutParamet er">
    <constructor-arg index="0" value="det"/>
    <constructor-arg index="1">
    <util:constant static-field="oracle.jdbc.OracleTypes.CURSOR"/>
    </constructor-arg>
    </bean>
    <bean class="org.springframework.jdbc.core.SqlOutParamet er">
    <constructor-arg index="0" value="status"/>
    <constructor-arg index="1">
    <util:constant static-field="java.sql.Types.VARCHAR"/>
    </constructor-arg>
    </bean>
    </list>
    </property>
    <property name="refCursorPosition" value="2"/>
    <property name="rowMapper" ref="beanRowMapper"/>
    <property name="preparedStatementSetter" ref="customParameterSetter"/>
    </bean>


    In CustomPreparedStatementSetter, I have

    public void setValues(PreparedStatement aPs) throws SQLException {
    CallableStatement lCallableStatement = (CallableStatement) aPs;
    lCallableStatement .setString(1, "13-Jun-2013");
    lCallableStatement .registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
    lCallableStatement .registerOutParameter(3, Types.VARCHAR);

    }


    I get the error

    13:32:59,240 ERROR AbstractStep:212 - Encountered an error executing the step
    org.springframework.jdbc.BadSqlGrammarException: Attempt to process next row failed; bad SQL grammar [{call SER_DTLS(?, ?, ?)}]; nested exception is java.sql.SQLException: Invalid column name
    at org.springframework.jdbc.support.SQLErrorCodeSQLEx ceptionTranslator.doTranslate(SQLErrorCodeSQLExcep tionTranslator.java:220)

    [CODE]

    How do I fix this error? and how do I get the resultset values out of the ref cursor?
Working...
X