ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Srini Gullipalli <srini.gullipa...@gmail.com>
Subject Re: Oracle - INSERT/UPDATE RETURNING INTO
Date Sat, 01 Mar 2008 15:53:56 GMT
Good question.  Found the answer :-)  Two ways. Tested both and seem to 
work.

(1) Anonymous PL/SQL Block and register output parameters using a 
CallableStatement.  Use ibatis <procedure> for the anonymous PL/SQL block

BEGIN
 UPDATE  aQueueTable SET  LOCK_IN = 'Y'
 WHERE LOCK_IN='N' and ROWNUM < 2
 RETURNING keyColumn INTO ?;
END;

(2) Oracle JDBC3.0 drivers support registering return parameters on 
prepared statements also.  This is not part of JDBC 3.0 spec :-(

statement = (OraclePreparedStatement)connection.prepareStatement(sql);
      statement.registerReturnParameter(1, OracleTypes.NUMBER);
if (statement.executeUpdate() > 0)
      {
        resultSet = statement.getReturnResultSet();
        while (resultSet.next())
        {
          System.out.println("Value for column 1 is " + resultSet.getLong(1));
        }
      }

iBatis team,
Any plans to support JDBC 3.0 generated keys ?

- Srini.

Larry Meadors wrote:
> How would you do it with JDBC?
>
> Larry
>
>
> On Fri, Feb 29, 2008 at 8:29 PM, Srini.Gullipalli
> <Srini.Gullipalli@gmail.com> wrote:
>   
>>  How can I achieve this using iBatis.  Not sure if this syntax is ANSI SQL,
>>  but Oracle supports this
>>
>>  UPDATE  aQueueTable SET  LOCK_IN = 'Y'
>>  WHERE LOCK_IN='N' and ROWNUM < 2
>>  RETURNING keyColumn INTO :key
>>
>>  Alternate use of this syntax :
>>
>>  INSERT INTO aTable (sequenceColumn, otherColumns...) VALUES ( aSeq.NEXTVAL,
>>  values...) RETURNING sequenceColumn INTO :key
>>
>>  This syntax eliminates an additional round trip to database ( compared to
>>  selectkey )
>>
>>
>>  --
>>  View this message in context: http://www.nabble.com/Oracle---INSERT-UPDATE-RETURNING-INTO-tp15772285p15772285.html
>>  Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
>>     


Mime
View raw message