Return value from Oracle function using Mybatis

Question

I have looked at a number of StackOverflow questions on this issue but can't find one that makes any sense. This one comes closest but doesn't show how to get the return value from the function.

Here's my mapper call:

public Long callMyFunction(@Param("recordId") Long recordId, @Param("otherId") Long otherId, @Param("date") Date date, @Param("comments") String comments);

Here's the mapper XML:

<select id="callMyFunction" parameterType="map" statementType="CALLABLE"  resultType="java.lang.Long">
    {  #{resultId,javaType=java.lang.Long,jdbcType=NUMERIC,mode=OUT} = call MYSCHEMA.MYPACKAGE.my_function(
    #{recordId,jdbcType=NUMERIC,mode=IN},
    #{otherId,jdbcType=NUMERIC,mode=IN},
    #{date,jdbcType=DATE,mode=IN},
    #{comments,jdbcType=VARCHAR,mode=IN})}
</select>

The call works, but the return value (resultId) is always null.

Can anybody spot the problem?

Have you tried it with recordId, javaType=long (native type) rather than Long (object wrapper)? — Steve Harrington
– Steve Harrington, Commented Dec 21, 2016 at 1:02

blackwizard · Accepted Answer · 2018-11-27 13:35:31Z

3

If you want to return directly the result, then the SQL call must be: SELECT MYSCHEMA.MYPACKAGE.my_function(...) FROM DUAL

If you want to keep with calling the function in the procedure call style, that means the result is an OUT parameter (you env declared it OUT). The minimum change would consist in adding a parameter to the mapper method signature: public Long callMyFunction(@Param("recordId") Long recordId, @Param("otherId") Long otherId, @Param("date") Date date, @Param("comments") String comments, @Param("resultIdContainer") Map<String, Long> resultIdContainer);

In the XML: forget the resultType, this is for selects. And the call: { #{resultIdContainer.resultId, javaType=java.lang.Long,jdbcType=NUMERIC,mode=OUT} = call ...

Not that I use here a map to contain the resutlId: an indirection is required: the function will write the parameter 'result' value somewhere you can read later (after your mapper call), you can also use a class with a resultId property.

edited Nov 27, 2018 at 13:35

answered Dec 21, 2016 at 9:18

blackwizard

2,0541 gold badge12 silver badges22 bronze badges

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

5 Comments

user1071914 Over a year ago

That was precisely what I needed, thank you very much!

Alex78191 Over a year ago

in the first variant ORA-00923: FROM keyword not found where expected

blackwizard Over a year ago

Fixed. It worked as it was in other DBMS, while in Oracle we must indeed select anything FROM DUAL

Alex78191 Over a year ago

What if my jdbcType is Cursor?

blackwizard Over a year ago

Should be call #{params.resultList, jdbcType=CURSOR, javaType=java.sql.ResultSet, resultMap=myResultMap, mode=OUT} = my_function() which is same principle as with procedure: call my_procedure(#{params.resultList, jdbcType=CURSOR, javaType=java.sql.ResultSet, resultMap=myResultMap, mode=OUT}). Note that params is used for input AND output. The mapper result type is void.

Collectives™ on Stack Overflow

Return value from Oracle function using Mybatis

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related