Error using Spring to call Oracle stored procedure

[J Miller]

We're using the spring framework to call an Oracle stored procedure, and it's giving me an error saying "wrong number or types of arguments in call". Here's what my dao looks like:

public List<Criteria> retrieveCriteria(String name)
{
    try
    {
        CriteriaStoredProcedure proc = new CriteriaStoredProcedure(ds, STORED_PROC);
        
        Map<String, Object> results = proc.execute(name);
        
        List<Criteria> result = (List<Criteria>) results.get("output_cursor");  
        
        return result;
    } catch (Exception e)
    {
        e.printStackTrace();
        return null;
    }
} 
    
private class CriteriaStoredProcedure extends StoredProcedure
{
    public CriteriaStoredProcedure(DataSource dataSource, String storedProcedureName)
    {
        super(dataSource, storedProcedureName);
        declareParameter(new SqlOutParameter("out_cursor", oracle.jdbc.OracleTypes.CURSOR, new CriteriaMapper()));
        compile();
    }

    public Map execute(String name)
    {
        Map inputs = new HashMap();
        inputs.put("nameIn", name);
        return execute(inputs);
    }

}

The spec for my stored procedure is:

PROCEDURE retreive_workspace_filters (
    NameIn      IN    WORKSPACE.WORKSPACE_N%TYPE,
    out_cursor  OUT curtype_generic);

So my stored proc has 1 input, and 1 output parameters. I'm only passing in a single input parameter, so I'm not sure why I'm getting that error. Any ideas?

[William P O'Sullivan]

I think you need to declare all parameters, IN,INOUT or OUT

WP

[J Miller]

I've updated the CriteriaStoredProcedure method to include the in parameter, and it doesn't seem to have made a difference. Still getting the same error. The only thing I can think of is that the datatype of the input param in Oracle is VARCHAR2, but there's no type for that under oracle.jdbc.OracleTypes.

public CriteriaStoredProcedure(DataSource dataSource, String storedProcedureName)
{
    super(dataSource, storedProcedureName);
    declareParameter(new SqlOutParameter("out_cursor", oracle.jdbc.OracleTypes.CURSOR, new CriteriaMapper()));
    declareParameter(new SqlParameter("nameIn", oracle.jdbc.OracleTypes.VARCHAR));
    compile();
}

[J Miller]

Found the problem. In my retrieveCriteria method, I had the name of the output cursor wrong. In Oracle it's "out_cursor", in that method I named it "output_cursor". Simple mistake.