How to use oracle returning into clause with insert in iBATIS 3.0

[rajesh kalaria]

Hi,
      Here is what I want to do  :
      Basically I am using Oracle 10g database and iBATIS 3.0. My issue is that, I want to do insert and wants to return the generated primary key(which is generated from oracle sequence) . To accomplish this right now I am using selectKey to get the next sequence value and then using it into the insert statement. This is basically two database calls, and I wants to do this in one database call. Oracle provides returning into clause by which we can return any specific values.
      How can I use returning into clause with insert statement ?

Thanks,
Rajesh
     

--
Rajesh Kalaria
Software Engineer
In2M Technologies Pvt Ltd.
Belapur, Navi Mumbai, Maharashtra

[Prashant Neginahal]

Have a look at this, i had same issue. I am using anonymous PL/SQL
block for the same.

http://groups.google.com/group/mybatis-user/browse_thread/thread/93b506d907ea2276

[rajesh kalaria]

Prashant,
    Thanks for your response. Actually I have seen this thread earlier as well, but for me it was not working. I am using this DTD "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd" in my XML mapper file.
     As soon as I try to use <procedure> element in my XML mapper file, it shows error. By looking into this DTD, it seems that there is no such procedure element.
     Did you use the same DTD ? I am using iBATIS 3.0.

Thanks,
Rajesh

[Prashant Neginahal]

I was using iBatis 2.3 as Spring 3.0 doesnt support MyBatis 3.0 yet.
In MyBatis 3.0 you can do something like below.

<insert id="insert_1" statementType="CALLABLE"
parameterType="test.ContactDdo">

begin insert into
SR1CUST1.CONTACT_INFORMATION( CONTACT_TYPE,
CONTACT_MODE,
VALUE)

values(#{contactType,mode=IN,jdbcType=CHAR},#{contactMode,mode=IN,jdbcType=CHAR},
#{value,mode=IN,jdbcType=VARCHAR}) returning contact_info_id,
updated_time into
#{contactId,
mode=OUT,jdbcType=BIGINT}, #{updatedTime,
mode=OUT,jdbcType=TIMESTAMP}; end;
</insert>

Thanks,
Prashant

On Jul 12, 2:17 pm, rajesh kalaria <rajesh.kala...@gmail.com> wrote:
> Prashant,
>     Thanks for your response. Actually I have seen this thread earlier as
> well, but for me it was not working. I am using this DTD "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd" in my XML mapper file.
>      As soon as I try to use <procedure> element in my XML mapper file, it
> shows error. By looking into this DTD, it seems that there is no such
> procedure element.
>      Did you use the same DTD ? I am using iBATIS 3.0.
>
> Thanks,
> Rajesh
>
> On Mon, Jul 12, 2010 at 11:53 AM, Prashant Neginahal

> <prashu.n...@gmail.com>wrote:

>
>
>
>
>
> > Have a look at this, i had same issue. I am using anonymous PL/SQL
> > block for the same.
>

> >http://groups.google.com/group/mybatis-user/browse_thread/thread/93b5...

> Belapur, Navi Mumbai, Maharashtra- Hide quoted text -
>
> - Show quoted text -

[rajesh kalaria]

Prashant,
      I tried this and it worked. I were looking this thing from long time. Really thanks a lot.

Regards,
Rajesh