execute Oracle stored procedure using MyBatis/Spring and parse results to a POJO

Question

I'm trying to execute an Oracle stored procedure, and map the results to a POJO, with mybatis/spring using annotations (i.e. not XML). The inputs/outputs for the stored procedure are:

P_INPUT     VARCHAR2         IN                         
P_OUTPUT    VARCHAR2         OUT            
P_COUNT     BINARY_INTEGER   OUT

And the POJO looks like this:

public class MyRecord {
    private String output;
    private Integer count;
    // getters and setters...
}

The snippet in this example, which says it's for Oracle, worked perfectly for me against a MySQL stored procedure. However, when I tried to execute the following against an Oracle stored procedure:

@Select(value= "{ CALL my_user.some_package.my_proc('${input}') }")
@Options(statementType = StatementType.CALLABLE)
@ResultType(MyRecord.class)
@Results({
                @Result(property="output", column="P_OUTPUT"),
                @Result(property="count", column="P_COUNT"),
         })
MyRecord parseMyInput(@Param("input") String input);

... it threw the following error:

bad SQL grammar []; nested exception is java.sql.SQLException: ORA-06550
PLS-00306: wrong number or types of arguments in call to 'my_proc'
PL/SQL: Statement ignored

From the error message, it sounds like the string being passed to the function may be incompatible with Oracle's VARCHAR2, or I'm somehow sending more than one argument. The string that I'm passing to the function, incidentally, is (12345).

Can you see what I'm doing wrong?

It is an Oracle procedure so, you have to pass those out parameters too. It works as reference. It would be something like: `{ CALL my_user.some_package.my_proc('${input}', '${out1}', '${out2}') }` But I have no Idea how to do it on the MyBatis annotations. — Jorge Campos, Jun 02 '16 at 00:50
Maybe this would help you: http://stackoverflow.com/questions/9215784/java-mybatis-stored-procedure-call-with-out-parameters — Jorge Campos, Jun 02 '16 at 00:53
Another one that even makes your question duplicate: http://stackoverflow.com/questions/24910795/mybatis-annotations-to-call-stored-procedure-and-get-out-params — Jorge Campos, Jun 02 '16 at 00:54
Thanks so much, @JorgeCampos. The answer in the duplicate question didn't work for me, but I'm trying your other suggestions right now. — Alex Woolford, Jun 02 '16 at 00:59
Have you tested passing the parameters as in the duplicate question? It would be something like: `{ CALL my_user.some_package.my_proc('${input, mode=IN, jdbcType=VARCHAR}', '${out1, mode=OUT, jdbcType=VARCHAR}', '${out2, mode=OUT, jdbcType=INTEGER }')` — Jorge Campos, Jun 02 '16 at 01:03

Alex Woolford · Accepted Answer · 2016-06-07T21:35:40.090

@Jorge was right. It was necessary to instantiate the POJO with the input parameter:

public class MyRecord {
    private String input;
    private String output;
    private Integer count;
    // getters and setters...
}

Then set the input parameter of the POJO and call the service:

    MyRecord myRecord = new myRecord();
    myRecord.setInput("myInput");

    dbMapperOracle.parseMyInput(myRecord);

The service calls the mapper, passing the POJO which contains variables for both the input (which has been set) and output parameters (which are null at this point):

public class DbServiceOracle {

    @Autowired
    @Qualifier("dataSourceOracle")
    private DbMapperOracle dbMapperOracle;

    public MyRecord parseMyInput(MyRecord params){
        dbMapperOracle.parseMyInput(params);
        return params;
    }
}

And the mapper maps the results returned from the stored procedure to the POJO:

@Select(value= "{ CALL my_user.some_package.my_proc(" +
        "#{input,   mode=IN,  javaType = java.lang.String,  jdbcType=VARCHAR}, " +
        "#{output,  mode=OUT, javaType = java.lang.String,  jdbcType=VARCHAR}, " +
        "#{count,   mode=OUT, javaType = java.lang.Integer, jdbcType=NUMERIC}, "
        ") }")
@Options(statementType = StatementType.CALLABLE)
@ResultType(MyRecord.class)
@Results({
                @Result(property="input", column="P_INPUT"),
                @Result(property="output", column="P_OUTPUT"),
                @Result(property="count", column="P_COUNT"),
        })
MyRecord parseMyInput(MyRecord params);

Credit to Sanjay Kumar, who put a more complete example in his GitHub account.

execute Oracle stored procedure using MyBatis/Spring and parse results to a POJO

1 Answers1