How do I write StoredProcedure sub-classes to call Oracle functions?

Question

I've written the following Spring JDBC API StoredProcedure sub-class:

class GetLdapPropertiesStoredProcedure extends StoredProcedure {

protected GetLdapPropertiesStoredProcedure(JdbcTemplate jdbcTemplate) {
    super(jdbcTemplate, "get_ldap_properties");
    setFunction(true);
    declareParameter(new SqlReturnResultSet("rs", new ProductPropertiesMapper()));
    declareParameter(new SqlParameter("in_ldap_code", Types.VARCHAR));
    compile();
}

public Properties execute(String productCode) {
    HashMap input = new HashMap();
    input.put("in_ldap_code", productCode);

    Map results = execute(input);

    Collection<Map.Entry<Object,Object>> entries = (Collection<Map.Entry<Object,Object>>) results.get("rs");
    Properties properties = new Properties();
    properties.entrySet().addAll(entries);
    return properties;
}

}

Which calls the following Oracle function:

FUNCTION get_ldap_properties (
in_ldap_code   IN VARCHAR2
)
RETURN rowset;

However, when I call the above I get the following Oracle exception:

java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'GET_LDAP_PROPERTIES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Can anyone please see what I'm doing wrong?

Answer 1

This is the statement you'll get for a function call (i.e. with setFunction(true)) :

{ ? = call get_ldap_properties(?) }

So you need to add a first out parameter for the return value. Try this instead:

setFunction(true);

// The return value parameter must be the first parameter that you declare.
declareParameter(new SqlOutParameter("RETURN_VALUE", OracleTypes.CURSOR, new ProductPropertiesMapper()));

Edit: Fixed the syntax to deal with the function's return value and the rowset when using Oracle according to Thomas Risberg's answer on Spring Community Forums.