Getting return value from oracle function using SimpleJdbcCall

Question

I have Oracle function defined as:

function get_user_by_term (inUserTerm number) return number;

Now I wan to call this function using Spring SimpleJdbCCall but not sure how I can read return value since I don't have out param in function. I can't change the Oracle function code.

Code I have so far in Java is:

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(dataSource)
        .withSchemaName("SCHMA").withCatalogName("PKG_USER")
        .withProcedureName("get_user_by_term");
Map<String, Object> inParamMap = new HashMap<String, Object>();
inParamMap.put("inUserTerm ", userTermId );
SqlParameterSource in = new MapSqlParameterSource(inParamMap);
simpleJdbcCall.execute(in);

Spring Documentation covers it here: 3.5.8. Calling a stored function using SimpleJdbcCall — cybersoft
– cybersoft, Commented Aug 29, 2018 at 21:15

αƞjiβ · Accepted Answer · 2015-06-08 22:03:09Z

5

After research I found following:

Instead of withProcedureName() method withFunctionName() method should be used.
Instead of execute() method use executeFunction() with return type parameter.

So complete code be like

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(dataSource)
        .withSchemaName("SCHMA").withCatalogName("PKG_USER")
        .withFunctionName("get_user_by_term");
Map<String, Object> inParamMap = new HashMap<String, Object>();
inParamMap.put("inUserTerm ", userTermId );
SqlParameterSource in = new MapSqlParameterSource(inParamMap);
Long userId = simpleJdbcCall.executeFunction(BigDecimal.class, in).longValue();

Hope this will help others too.

answered Jun 8, 2015 at 22:03

αƞjiβ

3,27615 gold badges64 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BlondCode · Accepted Answer · 2016-05-10 11:23:53Z

0

Calling executeFunction on the call worked for me, while i had to keep ".withProcedureName(...)" as it was. Check out your updated code:

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(dataSource)
     .withSchemaName("SCHMA")
     .withCatalogName("PKG_USER")
     .withProcedureName("get_user_by_term");
MapSqlParameterSource inParams = new MapSqlParameterSource()
     .addValue("inUserTerm ", userTermId );

Long returnVal = simpleJdbcCall.executeFunction(BigDecimal.class, inParams).longValue();

answered May 10, 2016 at 11:23

BlondCode

4,1971 gold badge22 silver badges21 bronze badges

Collectives™ on Stack Overflow

Getting return value from oracle function using SimpleJdbcCall

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related