Results 1 to 2 of 2

Thread: oracle sequences

  1. #1
    Join Date
    Jan 2006

    Cool oracle sequences

    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?

  2. #2
    Join Date
    Aug 2005
    The Garden State

    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.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center