views:

328

answers:

5
+3  Q: 

Oracle Sequences

I'm using a Sequence to generate an id for me, but I use that sequence from my Java application. So say for example my last Id is 200.

If I add an entry with .sql by by using 201 as an id instead of doing seq.nextval. What would happen when my java application calls seq.nextval? Is sequence smart enough to check the max available number or it will just return 201?

+3  A: 

It will just return 201, as the sequence has no idea what the numbers are used for.

Note: It may return, say, 220 if you have specified that the sequence has to cache values for some session (see the Oracle manual about CREATE SEQUENCE for more details)

andri
+2  A: 

Sequences just provide a way to "select" numbers that auto increment.

You will get 201 because they don't check anything, they just store the last value retrieved and when you query it again, it will return the next value in the sequence.

Juan Manuel
A: 

It will return 201.

You could also use nextval from JDBC, and then use that value to do the insert:

Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("select seq.nextval from dual");
rs.next();
int yourId = rs.getInt(1);

// then use yourId to do the insert

This way you can insert using a number, and also keep the sequence the way it should be.

Pablo Santa Cruz
A: 

What nextval returns on the next call from your Java app depends on a number of factors:

  1. If you run in a clustered environment, which node you next speak to. Each node will preallocate a pool of sequence values;
  2. Whether or not the node you're talking to has been restarted. If this happens the pool of sequence values will tend to be "lost" (meaning skipped);
  3. The step value of the sequence; and
  4. Whether are transactions have called nextval on the sequence.

Sequences are loosely ordered, not absolutely ordered.

But Oracle has no idea what you do with sequence values so if you insert a 201 into the database, the sequence will happily return 201 completely oblivious to the inserted value as the two are basically unrelated.

cletus
A: 

It is never a good idea to mix sequence-generated values with manual inserts because then everything gets mixed up.

Not sure if it helps in your situation, but remember that you can ask for the current value of the sequence (with seq.currval or similar) so that you can check if already exists in the table due to a manual insert and, if necessary, ask for another nextval

Jordi Cabot
Remember though that CURRVAL will raise an error unless you first call NEXTVAL.
Jeffrey Kemp