0

I have a web jsp app and a database. I would like to know how to properly handle sequences and inserts. For example, I have a table of bookings, with columns FLIGHT_ID, PASSPORT_ID. This pair is unique. Now, when I want to insert a new booking from data I received on the website, this is how I do it:

  1. Parse incoming data
  2. Get new BOOKING_ID value from a sequence (SELECT SEQ.nextval FROM dual;)
  3. Create an INSERT ...(new_booking_id,...) query
  4. Look for exceptions thrown by Oracle DBMS (most often unique constraint violation)

This works fine, but I "lose" some of the values of the sequence, because I receive them when calling select, but I don't use them.

What might work is to make the sequence go one step back everytime I catch an exception:

ALTER SEQUENCE SEQ INCREMENT BY -1;
SELECT SEQ.NEXTVAL FROM DUAL;
ALTER SEQUENCE SEQ INCREMENT BY 1;

But this doesn't seem like the best way to go. What is?

Martin Melka
  • 7,177
  • 16
  • 79
  • 138
  • 1
    Sequences will [_never_ generate a gap free sequence of numbers](http://asktom.oracle.com/pls/asktom/f?p=100:11:0%3a%3a%3a%3aP11_QUESTION_ID:530735152441), a perfect _1, 2 .. n_. If you need them for this purpose then you're using them wrong... There should rarely, if ever, be a need for this functionality, especially in the scenario you mention. – Ben Nov 08 '13 at 16:43
  • I am only using it as IDs so gaps don't really matter to me, but I supposed I should be efficient with sequences. If it is OK to just let some numbers pass, then I withdraw my question – Martin Melka Nov 08 '13 at 17:14

1 Answers1

0

if only concern is losing some sequence values, you can generate only necessary once by checking before executing INSERT. perform the select from same table and generate newbookingID only for records that going to make successfull insert. (As you mentioned the exception can be due to Unique constraint so you already know what values are supposed to be unique)

Anup Shah
  • 1,256
  • 10
  • 15