I am making use of a oracle sequence(seq_id).The values from the sequence serve as primary key for another table(names).In a servlet I am inserting values in the names table using a preparedstatement instance(ps).To set the value for the ID column of names table I use ps.setString(1,seq_id.nextval).Now I want to get the inserted ID value from names(keeping in mind that there is no other primary key in the names table) so that I can pass this value to the next servlet.
Plz tell me if there is a way to retrieve the inserted ID value in this case.Is there any method like ps.getParameter or something to retrieve the values provided to ps?
just to clarify your question, you're not populating the table via trigger then right? that's typically the favored approach but either work actually.
anyways, there's a before insert way and an after insert way of getting the id, i'll describe them both.
- after insert: select max(id-column) from your-table; this assumes that your sequence grows, not shrinks. (if it shrinks use min) and parse the result set.
- before insert, get the id from the sequence via select sequence_name.nextval from dual; and insert that value. i don't usually recommend this method unless it's for a smaller system.
other than that, I don't think there's anything special in the JDBC that is for handling sequence generation. there might be something native to oracle though, i'd check on OTN.