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
<[EMAIL PROTECTED]> 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.
