Search code examples
javastored-proceduresmybatisout-parameters

MyBatis annotations to call Stored Procedure and get Out Params

I am using MyBAtis-3 with MyBAtis-Spring. When i tried to call a stored procedure that returns more than one out params using MyBatis annotations. I don't get anything, I can see that the input parameter is passed to the SP in the logs and it hangs in there with no progress nor exception thrown.

PFB the Oracle Stored Procedure which i am trying to access from MyBAtis,

create or replace PROCEDURE C2C.GET_DATA
(
  "IN_PARAM1" IN NUMBER,
   "OUT_PARAM2" OUT SAMPLETABLE.COL2%TYPE,
   "OUT_PARAM3" OUT SAMPLETABLE.COL3%TYPE,
   "OUT_PARAM4" OUT SAMPLETABLE.COL4%TYPE
  )  AS

  BEGIN
    SELECT PARAM2,PARAM3,PARAM4 INTO 
    OUT_PARAM2,OUT_PARAM3,OUT_PARAM4
    FROM C2C.SAMPLETABLE WHERE PARAM1=IN_PARAM1 ;  

  END C2C.GET_DATA;

PFB the mapper interface method,

@Select(value= "{ CALL  C2C.GET_DATA( #{param1, mode=IN, jdbcType=INTEGER},#{param2, mode=OUT, jdbcType=VARCHAR},#{param3, mode=OUT, jdbcType=INTEGER},#{param4, mode=OUT, jdbcType=INTEGER})}")
@Options(statementType = StatementType.CALLABLE)
public void getData(Test test);

The Test object contains the parameters passed as input in the Stored Procedure Call statement.

When i execute this, it gets hanged over here,

main Slf4jImpl 
==>  Preparing: { CALL C2C.GET_DATA(?,?,?,?)} 

main Slf4jImpl 
==> Parameters: 60(Integer)
Solution

  • Surprisingly if i use @Insert annotation it is working as expected. Some weird behavior while calling Stored Procedures. Let me know if any one else has a good solution other than this.