Spring: Unable to determine the correct call signature - multiple procedures/functions/signatures

Question

I'm trying to get data from an Oracle stored procedure. The problem is that in our database there is a function and a procedure with the same name and same parameters.

When I try to call it:

@Autowired
    public void setDataSource (@Qualifier("dataSource") DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.functionGetSomeCode = new SimpleJdbcCall(jdbcTemplate)
                .declareParameters(new SqlOutParameter("RETURN", OracleTypes.NUMBER))
                .withFunctionName("get_some_code").withSchemaName("XXX").withCatalogName("some_pkg");
    }

    public Integer getSomeCode (String incoming) {
        SqlParameterSource incomingParameters = new MapSqlParameterSource().addValue("incoming", incoming);
        return functionGetSomeCode.executeFunction(Integer.class, incomingParameters);
    }

I get an exception:

springframework.dao.InvalidDataAccessApiUsageException: Unable to determine the correct call signature - multiple procedures/functions/signatures

Is there a way to handle this situation without asking the DBA to rename the function / procedure to something different?

Answer 1

I've been able to call functions and procedures that have the same name but it doesn't always work. In your example it looks like you aren't declaring the input parameters. Try declaring the input and output parameters with types as closely matched to the package declaration as possible. If that still doesn't work you can try turning off ProcedureColumnMetaDataAccess but be sure to test.

Here is an example:

protected SimpleJdbcCall buildJdbcCall(JdbcTemplate jdbcTemplate) {
    SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate)
            .withSchemaName(schema)
            .withCatalogName(catalog)
            .withFunctionName(functionName) 
            // can use withProcedureName(procedureName) for procedures 
            //.withReturnValue()
            //  .withoutProcedureColumnMetaDataAccess()  // may need this
            .declareParameters(buildSqlParameters());
    return call;
}

public SqlParameter[] buildSqlParameters() {
    return new SqlParameter[]{
        new SqlParameter("p_id", Types.VARCHAR),
        new SqlParameter("p_office_id", Types.VARCHAR),
        new SqlOutParameter("l_clob", Types.CLOB)
    };
}

Answer 2

In Case, you do not have return variable, the below code can help. I was facing the same issue in which my code was working without declaring the out params, as functions doesnot have out params. I resolved this issue by declaring the out param as return. Code is as follows.

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate.getDataSource()).withCatalogName("PACKAGE_NAME")
                .withFunctionName("MY_FUNCTION_NAME").withoutProcedureColumnMetaDataAccess().declareParameters(
                        new SqlOutParameter("RETURN", Types.TIMESTAMP),
                        new SqlParameter("XYX_Y", Types.DATE),
                        new SqlParameter("HH_HDU", Types.VARCHAR)
                        );
        jdbcCall.compile();

        MapSqlParameterSource in = new MapSqlParameterSource();
        in.addValue("XYX_Y", myDate);
        in.addValue("HH_HDU", "PQR");
        java.sql.Timestamp date =  jdbcCall.executeFunction(java.sql.Timestamp.class, in);

Answer 3

I was also facing the same issue. I have 2 processors with same name with one different params “lang_code_in”. Looks like the issue is due to this only and it is coming because of Spring/Spring boot application. So I added after ".withoutProcedureColumnMetaDataAccess()."

    final SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(dataSource).withCatalogName(catalogName)
            .withProcedureName(procedureName)
            .withoutProcedureColumnMetaDataAccess().
            .declareParameters(buildPullOrderSqlParameters());

But after this it starts giving below error: org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call NPIADM.CCM_JIGSAW_TEMPLATE_PKG.GET_UPC_XML_CONTENT(?, ?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GET_UPC_XML_CONTENT' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Then I added all the in and out params declareParameters() object like below and it started working fine. Below code have P_ORDER_NUMBER_I is IN params and p_err_msg_o is out params

public SqlParameter[] buildPullOrderSqlParameters() {
    return new SqlParameter[]{
            new SqlParameter("P_ORDER_NUMBER_I", Types.VARCHAR),
            new SqlOutParameter("p_err_msg_o", Types.VARCHAR)
    };
}