I have a stored procedure in a package which has it defined with three different method signatures in the same package. They vary by only a few parameters (each calls the next with a few default values). I've over-simplified the list of columns for the sake of brevity here. I have about 55 fields in the procedure I'm calling.
I am using the Spring class called "MapSqlParameterSource" to define the fields values I am providing:
SqlParameterSource params = new MapSqlParameterSource()
.addValue("pi_session_id", piSessionId)
.addValue("pi_site_id", piSiteId)
.addValue("pi_address_id", piAddressId)
.addValue("po_status_cd", null)
.addValue("po_status_mg", null);
I'm using a simple jdbc call to define the stored procedure that I want to call:
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withSchemaName("myschema")
.withCatalogName("address_package")
.withProcedureName("p_post_address");
And then I execute the procedure with this call:
Map<String, Object> resultsMap = jdbcCall.execute(params);
And I typically log out the results for fun while I'm dev'ing these up.
for (String key : resultsMap.keySet()) {
logger.info("p_post_address - Key: " + key + " object: " + resultsMap.get(key));
}
And then I access the output fields like this:
String statusMessage = (String) resultsMap.get("po_status_mg");
BigDecimal statsCd = (BigDecimal) resultsMap.get("po_status_cd");
This works perfectly for Procedures that aren't overloaded! However, when I call this particular procedure, it gives me this error:
org.springframework.dao.InvalidDataAccessApiUsageException: Unable to determine the correct call signature - multiple procedures/functions/signatures for 'P_POST_ADDRESS'
Then I attempted to get tricky, and specify exactly which parameters should be used to call the stored procedure, so that it could match the overloaded method I wanted.
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withSchemaName("myschema")
.withCatalogName("address_package")
.withProcedureName("p_post_address");
jdbcCall.addDeclaredParameter(new SqlParameter("pi_session_id", OracleTypes.NUMBER));
jdbcCall.addDeclaredParameter(new SqlParameter("pi_site_id", OracleTypes.VARCHAR));
jdbcCall.addDeclaredParameter(new SqlParameter("pi_address_id", OracleTypes.NUMBER));
jdbcCall.addDeclaredParameter(new SqlOutParameter("po_status_cd", OracleTypes.NUMBER));
jdbcCall.addDeclaredParameter(new SqlOutParameter("po_status_mg", OracleTypes.VARCHAR));
This resulted in the same error.
org.springframework.dao.InvalidDataAccessApiUsageException: Unable to determine the correct call signature - multiple procedures/functions/signatures for 'P_POST_ADDRESS'
I eventually found out I need to add this option:
jdbcCall.withoutProcedureColumnMetaDataAccess();
And that works! But I have no idea why.
Could someone explain why you need to add the declared parameters addDeclaredParameter and also to call withoutProcedureColumnMetaDataAccess?