I have the code below. Basically I'm trying to retrieve data from procedure to the Spring project. I got two out parameter which are Struct and Array which makes the process complicated. But for the inOut Array parameter i have Unable to locate the corresponding parameter value for 'p_process_results' within the parameter values provided: [p_claim_id, p_sf_no, p_user] this error. I couldn't find what's wrong with the Component class.
This is the code I'm trying to transform to the Spring Project.
String statement = "BEGIN CUSTOMER.alz_exw_clm_transaction_utils.GET_MX_CLAIMS_INFO(?,?,?,?,?); END;";
conn = DbUtils.getConnection();
stmt = conn.prepareCall(statement);
stmt.setInt(1, claimId);
stmt.setInt(2, sfNo);
stmt.setString(3, username);
stmt.registerOutParameter(4, OracleTypes.STRUCT, ClaimMxStruct.recTypeName);
stmt.registerOutParameter(5, OracleTypes.ARRAY, ProcessResultType.tblTypeName);
stmt.execute();
Spring Repository
public class ClaimMxRepository extends NamedParameterFactory{
@Qualifier("getClaimMxInfo")
private final SimpleJdbcCall getClaimMxInfo;
public ClaimMx getClaimMx(ClaimMxReq request) throws SQLException {
Map<String, Object> params = new HashMap<>();
params.put("p_claim_id", request.claimId);
params.put("p_sf_no", request.sfNo);
params.put("p_user", request.username);
Map<String, Object> result = getClaimMxInfo.execute(params);
//ClaimMxStruct struct = (ClaimMxStruct) result.get("p_claim_mx_rec");
//Array arr = (Array) result.get("p_process_results");
return null;
}
}
Component
@Bean(name = "getClaimMxInfo")
public SimpleJdbcCall getClaimMxInfo() {
return simpleJdbcCallService.simpleJdbcCallWithCustomerSchema("ALZ_EXW_CLM_TRANSACTION_UTILS", "GET_MX_CLAIMS_INFO")
.declareParameters(
sqlParameterWithNumeric("p_claim_id"),
sqlParameterWithNumeric("p_sf_no"),
sqlParameterWithVarChar("p_user"),
new SqlOutParameter("p_claim_mx_rec", STRUCT, "CUSTOMER.CLAIM_MX_REC", new SqlReturnStruct(ClaimMxStruct.class)),
new SqlInOutParameter("p_process_results", ARRAY,"CUSTOMER.PROCESS_RESULT_TABLE", new SqlReturnArray()));
}
Procedure
CREATE OR REPLACE PACKAGE BODY CUSTOMER."ALZ_EXW_CLM_TRANSACTION_UTILS"
IS
PROCEDURE get_mx_claims_info (
p_claim_id NUMBER,
p_sf_no NUMBER,
p_user VARCHAR2,
p_claim_mx_rec OUT customer.claim_mx_rec,
p_process_results IN OUT customer.process_result_table
)
IS
