Spring SimpleJdbcCall Oracle sql/pl - Unable to determine the correct call signature - multiple procedures/functions/signatures

Refresh

April 2019

Views

930 time

1

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?

0 answers