java - 无法使用 StoredProcedureItemReader 读取 REF_CURSOR

Question

我尝试调用存储过程

create or replace PROCEDURE "PROC"(cur OUT SYS_REFCURSOR)

用这个 bean 。

@Bean
    StoredProcedureItemReader<?> itemReader() {
        StoredProcedureItemReader<?> storedProcedureItemReader = new StoredProcedureItemReader<>();
        storedProcedureItemReader.setDataSource(dataSource);
        storedProcedureItemReader.setProcedureName("PROC");
        storedProcedureItemReader.setRefCursorPosition(1);
        storedProcedureItemReader.setRowMapper(new ColumnMapRowMapper());
        storedProcedureItemReader.open(new ExecutionContext());
        return storedProcedureItemReader;
    }

但是我明白了

org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.open(AbstractItemCountingItemStreamItemReader.java:134)
    ... 54 more
Caused by: java.sql.SQLException: Invalid column index
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:125)
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:304)
    at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:393)
    at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1569)
    at org.springframework.batch.item.database.StoredProcedureItemReader.openCursor(StoredProcedureItemReader.java:202)
    ... 56 more

如何只用一个游标调用 PROC 并迭代结果？

Answer 1

我认为你需要为 out 参数 cur 添加一个参数给 reader。在打开 storedProcedureItemReader 之前尝试添加以下行:

    storedProcedureItemReader.setParameters(new SqlParameter[] { new SqlParameter("cur", OracleTypes.CURSOR) });