Please use the google group to ask questions - thanks.

by user2 07 Nov 14:56
How do you get the inserted seq back for Oracle?
using the RETURNING clause
19 Nov 03:51
by Rob

Actually I pulled out the Oracle9 specific code a little time back. I upgraded to Oracle10 and it is much better for support of the JDBC standards.

Oracle10 supports getGeneratedKeys and this is very important if you want transparent statement batching.

The code required to workaround Oracle9 issues such as using the RETURNING clause (and hence using CallableStatement) became a tough decision and I decided to pull it.

Oracle9 should still work but instead a sequence is fetched prior to the insert. In Ebean this is basically considered a IdGenerator as the Id is determined prior to the insert (just as you would is you used UUID).

19 Nov 03:57
by Rob

Just to note, if you use Oracle9 and are looking to insert a very large number of rows, then neither the default solution nor using CallableStatement and RETURNING clause are particularly good.

The default of select seqName.nextval from dual; prior to every insert obviously incurs the additional select for every row inserted.

CallableStatement is not batchable according to the Oracle documentation and so using the RETURNING clause removes the additional select but doesn't allow acutal statement batching.

The solution I would recommend for Oracle9 users doing a large number of inserts is to use UpdateSql... and put the seqname.nextval in the actual sql.

Obviously this is not an ORM way of doing things, but its unlikely that you want the 'beans' back after insert to later on perform an update. Certainly this is likely the most performant way to do this apart from using raw JDBC.

Create a New Topic

Introduction User Guide (pdf) Install/Configure Public JavaDoc Whitepapers
General Database Specific Byte Code Deployment Annotations Features
Top Bugs Top Enhancements