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?

Answer 1

@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.