Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (athena.apache.org: domain of fcferrari@gmail.com
 designates 209.85.200.174 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=message-id:date:from:to:subject:mime-version:content-type
         :content-transfer-encoding:content-disposition;
        b=Zd/BHQp1OZkyQpNNESWkDvq93h+MhPBCVlRUkeZ2bCzzYkp6OylnRgzQzIdo5HFrFz
         AmbirbdCAwLFaMHzgrqFp3FOTts0v8BhAp0ajylBuerIl8TVWwRLJ6eb1kT/oVmRdCPU
         Z2M2B6ssdNk9UrIZIdMCZx8zYqVhlR7VZean4=
Message-ID: <48c6964d0808201822x4e8ff95fu2a43d0014e6c7f55@mail.gmail.com>
Date: Wed, 20 Aug 2008 22:22:44 -0300
From: "Fabiano Ferrari" <fcferrari@gmail.com>
To: user-java@ibatis.apache.org
Subject: How to run a stored procedure with iBATIS.
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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