CODEWITHSUNDEEP
javaspringoracle-databasehibernatejpa
asenovm
asenovm

Reputation: 6517

Invoking Oracle stored function from Spring

I have an Oracle database and a stored function in it that has the following signature:

FUNCTION fnc_a_b1(pv_a10  IN VARCHAR2,
                          pn_b OUT NUMBER,
                          pv_c  OUT VARCHAR2) RETURN VARCHAR2 DETERMINISTIC

There is a Java/Spring backend that should invoke this function and so far I have done the following:

public class MyStoredFunction extends StoredProcedure {

    public MyStoredFunction(final DataSource datasource) {
        super(datasource, "package_name.fnc_a_b1");
        setFunction(true);
        declareParameter(new SqlParameter("pv_a10", Types.VARCHAR));
        declareParameter(new SqlOutParameter("pn_b", Types.NUMERIC));
        declareParameter(new SqlOutParameter("pv_c", Types.VARCHAR));
        compile();
    }
}

I execute it like:

final Map<String, Object> params = new HashMap<>();
params.put("pv_a10", "123123");
new MyStoredFunction(datasource).execute(params);

and I get

java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'FNC_A_B1'

Any idea why? Everything seems ok (sorry for the name obfuscation - digits in the names are actually present as well as the pv/pn prefixes but the ones from the db coincide with the ones in the backend)

Upvotes: 0

Views: 298

Answers (1)

Deroude
Deroude

Reputation: 1112

Your function returns VARCHAR2. That's basically another OUT parameter. Adding it to your declarations should fix this.

declareParameter(new SqlOutParameter("return_param", Types.VARCHAR));

Upvotes: 1

Related Questions