How to run a stored procedure with iBATIS.

[Fabiano Ferrari]

Hi, all.

I need some help to run a stored procedure with iBATIS. Sorry if this
question has already circulated in list.

When trying to run the procedure, I run into a problem. My database is
Oracle 10g Express Edition and I'm currently using iBATIS 2.0 Beta 4.

This is the output I get when invoking the procedure within a JUnit test case:

  1) 
testExecuteQueryProcedure(com.ibatis.sqlmap.engine.execution.SqlExecutorTest)com.ibatis.common.jdbc.exception.NestedSQLException:
  --- The error occurred in com/ibatis/sqlmap/maps/OracleProc-modified.xml.
  --- The error occurred while applying a parameter map.
  --- Check the getAccountEmail.
  --- Check the parameter mapping for the 'email' property.
  --- Cause: java.sql.SQLException: Cannot perform fetch on a PLSQL
statement: next


-------------------------------------------------

My JavaCode is:

  Map param = new HashMap();
  param.put("id",    new Integer(1));
  param.put("email", new String());

  String email = (String) sqlMap.queryForObject(
"getAccountEmailViaProcedure", param);



My SqlMap is:

  <parameterMap id="getAccountEmail" class="map" >
    <parameter property="id"    jdbcType="INTEGER"
javaType="java.lang.Integer" mode="INOUT"/>
    <parameter property="email" jdbcType="VARCHAR"
javaType="java.lang.String"  mode="OUT"/>
  </parameterMap>

  <procedure id="getAccountEmailViaProcedure" parameterMap="getAccountEmail">
     {call get_account_email ( ? ,? )}
  </procedure>


My Oracle procedure (which works fine when manually invoked) is:

  PROCEDURE get_account_email
    (id IN INTEGER, email OUT VARCHAR)
  IS
  BEGIN
    SELECT acc_email
    INTO email
    FROM account2
    where ACC_ID = id;
  END;

-------------------------------------------------

Thanks for any help.

  Fabiano