MyBatis calling Oracle stored procedures

Lesson learned today: Use MyBatis update statement for calling Oracle stored procedures with input and output parameters.

Fragment of my MyBatis mapper xml file before:

  <select id="getPasswordVerificationStatus" statementType="CALLABLE" parameterType="getPasswordVerificationStatus">
  {call WEB.IFC_CORE.GETUSERPASSWORDSTATUS(
   #{idEmp,mode=IN},
   #{login,mode=IN},
   #{password,mode=IN},
   #{expiry,jdbcType=INTEGER,mode=OUT},
   #{expiryCode,jdbcType=INTEGER,mode=OUT})}
 </select>

I've tried to implement a stored procedure call (using Oracle jdbc driver and Oracle Universal Connection Pool in background), taking an inspiration from some working examples. According the Ibatis / myBatis migration guide we can use select, update or insert when calling stored procedure. Since my procedure only asked for a status and didn't insert or modify any data I expected a select element to be the appropriate one to use.

Caused by: java.lang.RuntimeException: unexpected invocation exception: unexpected invocation exception: null at oracle.ucp.jdbc.proxy.PreparedStatementProxyFactory.invoke ...

The exception I got was caused by the invocation of getResultSet method on oracle.ucp.jdbc.proxy.CallableStatementProxyFactory - it took me a while to debug the code since it is using a lot proxy objects.

Finally I've found that somebody had the same problem using Ibatis and there was also an issue in MyBatis tracker.

Final working example:

  <update id="getPasswordVerificationStatus" statementType="CALLABLE" parameterType="getPasswordVerificationStatus">
  {call WEB.IFC_CORE.GETUSERPASSWORDSTATUS(
   #{idEmp,mode=IN},
   #{login,mode=IN},
   #{password,mode=IN},
   #{expiry,jdbcType=INTEGER,mode=OUT},
   #{expiryCode,jdbcType=INTEGER,mode=OUT})}
 </update>

I'm not sure if the issue is related to the Oracle jdbc driver only or it is a general issue. If you have tested it in other environments don't hesitate to share your experiences in comments. If it is not the Oracle specific problem, please share use cases of select when calling a stored procedure.