How to return the newly generated sequence id for an INSERT statement

[Chethan Kumar Baliga]

A record is to be inserted into a table with a sequence for the primary key. The newly inserted sequence value is to returned on successful insertion. Is it possible to do all this in a single statement (say executeUpdate or any other) using java.sql.* ?
E.g.: - A student record is to be inserted into the STUDENT table. There is a sequence (by name Student_ID_SEQ) on the primary key Student_ID. Student_ID_SEQ.nextval will generate the new sequence id which will be provided as input to the SQL statement (say statement.executeUpdate) along with other student attribute values. On insertion the created sequence id should be returned. And all this should happen in a single statement (single call to database). Stored Procedures can accomplish this. But is this feasible without the use of Stored Procedures?
Thanks.

[Leslie Chaim]

How about have a helper method which would retrun the value from

select Student_ID_SEQ.currval
  from dual

[SJ Adnams]

Leslie your planning to fail
Try this:
"begin insert into foo(x,y,z) values ( x_seq.nextval,?,?) return x into ?; end;"

Use CallableStatement and registerOutParameter(). Hopefully that should point you in the right direction.
HTH Simon

[Leslie Chaim]

ouch! You did not cause a bad day � yet
Simon,
If I understood the question correctly, Chethan wants to preclude the use of procedures
The other thing that Chethan wants is to make only one trip to the database.
So � you have two choices:
Create a procedure (or function) where the new sequence is returned and make only one trip to the database.
Or
Make two trips to the database using sequence_name.currval. I think the later is easier and simpler.
Did I miss anything?
[ May 19, 2003: Message edited by: Leslie Chaim ]

[SJ Adnams]

Well its not really a 'stored' proceedure. You send the PL/SQL to the database and you get the new id back.
Oracle uses the same execution engine for PL/SQL and plain SQL so I think the discussion is academic.
The problem with two trips is that (I think) sequences cannot be rollbacked (hmmn maybe I should try this before posting) so even if you kept the two sql statements in the same transaction, you cannot be sure that another transaction has not altered the sequence.
From a performance perspective, reducing the number of trips (as opposed to what you do in each trip) is better also.
Simon
Now I'm going to try rolling back sequences...

[Chethan Kumar Baliga]

Thanks Simon, your solution works, bang on target!
Thanks to Leslie too.
:-)
Regards,
Chethan.

[SJ Adnams]

damn, I was hoping to cause you a bad day

[Leslie Chaim]

damn, I was hoping to cause you a bad day
Regardless, whether it's really bad dependes on the other party
BTW, you can not rollback sequences. The way I see it, rollback is meant for transaction processing. Fetching the next seq is working with an object.

[SJ Adnams]

Leslie,
I tried the rolling back sequences and it doesn't work like you said. This means that if there are two threads hitting the database inserting records, followed by

select Student_ID_SEQ.currval from dual

the sequence number can be updated by another thread.
I use the begin ... return ... into syntax a lot in my application & have had no problems.
Hey I think we both learnt something